본문 바로가기

Stored Procedure

 (6)
[mssql] sp명으로 정의 가져오기 SELECT OBJECT_NAME(object_id) AS sp_name, OBJECT_DEFINITION(object_id) AS sp_context FROM sys.procedures WHERE OBJECT_DEFINITION(object_id) like '%{0}%'
[mssql] 특정 문자열 포함 sp 명 반환 SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%{{문자열}}}%' AND ROUTINE_TYPE = 'PROCEDURE' order by ROUTINE_NAME
[mysql] R stored procedure 기본형 CREATE DEFINER=`root`@`%` PROCEDURE `new procedure`(IN `idxParam` CHAR(6) # 파라메터들 , IN `page` INT UNSIGNED , IN `offset` INT UNSIGNED , OUT `total` INT UNSIGNED ) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN IF `offset` IS NULL THEN SET `offset` = 2; END IF; IF `page` IS NULL THEN SET `page` = 0; ELSE SET `page` = `page` * `offset`; END IF; #상세보기 IF `idxParam..
[mysql] C/U/D stored procedure 기본형 CREATE DEFINER=`root`@`%` PROCEDURE `new procedure`(IN `procTypeParam` VARCHAR(20) , IN `idxParam` INT , OUT `identity_idx` INT , OUT `error_code`INT , OUT `error_message`VARCHAR(1000) ) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @error_code = MYSQL_ERRNO, @text = MESSAGE_TEXT; SET `error_code` = @error_code; SET `error_message` =..
[mssql] CONVERT Datatime to string DECLARE @index INT = 100 DECLARE @result VARCHAR(30) WHILE @index < 132 BEGIN BEGIN TRY SET @result = CONVERT(VARCHAR(30), GETDATE(), @index) PRINT convert(char(3), @index) + ', ' + @result END TRY BEGIN CATCH print convert(char(3), @index) + ', ' + 'NOT_CONVERTED' END CATCH SET @index = @index + 1 END 100, 06 26 2017 6:33PM 101, 06/26/2017 102, 2017.06.26 103, 26/06/2017 104, 26.06.2017 105, 26..
[EntityFramework] Support Spatial Data for stored procedure on SqlServer 2008R2 edmx designer 로 sp 를 add 하면 spatial data type ( geometry/geography ) 의 class 가 edmx 에 생성되지 않습니다. solution #1 Install service pack / feature pack - https://goo.gl/q2xUTf solution #2 To add scalar property in *.tt class - https://goo.gl/xZJA42 2번 추천합니다.

티스토리툴바