목록DB/MSSQL (16)

develog

[MSSQL] DATETIME, VARCHAR 변환

▶ VARCHAR > DATETIMESELECT CONVERT(DATETIME, '20170407'); ▶ DATETIME > VARCHAR-- 112 => YYYYMMDD SELECT CONVERT(VARCHAR, CONVERT(DATETIME, '2017-04-07'), 112); ▶ DATETIME 날짜 기간 검색 -- 20120823 일자 검색 SELECT * FROM MY_TABLE WHERE 1=1 AND REG_DT >= CONVERT(DATETIME, '20120823') AND REG_DT < CONVERT(DATETIME, '20120823') + 1 ;
DB/MSSQL 2017. 4. 7. 13:49
[MSSQL] Bind Variables

in SQL Server Management Studio DECLARE @AA INT SET @AA = 20 SELECT @AA DECLARE @AA VARCHAR(5) DECLARE @BB VARCHAR(3) SET @AA = '11111' SET @BB = '222' SELECT * FROM ( SELECT '11111' AA, '111' BB UNION ALL SELECT '22222' AA, '222' BB UNION ALL SELECT '11111' AA, '222' BB ) Z WHERE AA = @AA AND BB = @BB ;
DB/MSSQL 2017. 3. 24. 09:51
[MSSQL] Procedure 생성, 수정, 실행, 삭제

Procedure 생성 CREATE PROCEDURE SP_TEST @AA VARCHAR(10), @BB VARCHAR(10), @CC VARCHAR(10) OUTPUT AS DECLARE @RET VARCHAR; SET @RET = '33'; SET @CC = @AA + @BB + @RET; GO Procedure 수정 ALTER PROCEDURE ... Procedure 실행 DECLARE @RET VARCHAR(10); EXEC SP_TEST @AA = '11', @BB = '22', @CC = @RET OUTPUT; PRINT @RET; Procedure 삭제 DROP PROCEDURE SP_TEST;
DB/MSSQL 2016. 12. 13. 18:34
[MSSQL] Function 생성, 수정, 실행, 삭제

Function 생성 CREATE FUNCTION FN_TEST(@P_MSG VARCHAR(10)) RETURNS VARCHAR AS BEGIN DECLARE @V_RETURN VARCHAR(100); DECLARE @V_STR VARCHAR(10); SET @V_STR = 'abc'; SET @V_RETURN = concat(@V_STR, @P_MSG); RETURN @V_RETURN; END Function 수정 ALTER FUNCTION ... Function 실행 SELECT DBO.FN_TEST('ZZZ') RET_MSG Function 삭제 DROP FUNCTION FN_TEST;
DB/MSSQL 2016. 12. 13. 18:14
[MSSQL] 테이블 전체 주석 조회

--테이블 주석 조회SELECTNAME, (SELECT VALUE FROM SYS.EXTENDED_PROPERTIES WHERE MAJOR_ID = A.ID AND MINOR_ID = 0 ) COMMENTFROM SYSOBJECTS AWHERE RTRIM(A.XTYPE) = 'U'ORDER BY NAME
DB/MSSQL 2015. 4. 9. 19:13
[MSSQL] 주석

--한줄 주석 /*블럭 주석*/
DB/MSSQL 2015. 4. 9. 19:03
Prev 1 2 3 Next