메뉴 건너뛰기

Dev tips

DataBase [MSSQL] MS-SQL 동적쿼리 이용하기

taknim 2009.12.28 23:18 조회 수 : 16990 추천:3492

http://sway.tistory.com/430

기본적인 동적 쿼리 - INSERT

CREATE PROC [dbo].u_sp_insert

     @board_id varchar(128),
     @writer  varchar(24),
     @pwd  varchar(24),
     @content text,

AS

BEGIN

     DECLARE
          @strSql  nvarchar(128),
          @param  nvarchar(128)

 

     SET @strSql = 'INSERT INTO ' + @board_id + '(writer, [password], content) 
                           VALUES(@p_writer, @p_password, @p_content)'

 

     SET @param = '@p_writer varchar(24), 
                           @p_password varchar(24), 
                           @p_content text'

 

     EXECUTE sp_executesql @strSql, @param, 
                                                      @p_writer  = @writer,
                                                      @p_password  = @pwd,
                                                      @p_content = @content

 

END

OUTPUT 을 사용하는 동적 쿼리

SET @strSql = 'SELECT @p_value = MAX(idx) from ' + @board_id + ' WHERE ref = @p_wheredata'
  
SET @params =
'@p_wheredata  int,
                        @p_value as int OUTPUT '

 

EXEC sp_executesql @strSql, @params,

                                           @p_value  = @value OUTPUT,
                                           @p_wheredata = @wheredata