[MSSQL] sp_executesql 사용법.

asp.net 을 하면서 게시판을 제작하다 보면
정말 초보자 수준에서 보게 되면, 게시판 같은 경우
같은 테이블을 사용해야 하는 상황이 생기는데, 이런 경우 저장 프로시저를 각 테이블 별로
제작 하게 되면 작업사항도 번거로워 지는걸 발견해서
저장 프로시저를 동적 테이블을 구성해 볼려고 했다.

쩝.. 세상 프로그램은 쉬운게 없더라..

가장 쉽게 저장 프로시저를 구성하면 아래와 같아 진다.

ALTER PROCEDURE [dbo].[sp_ReplyListNum]
(
 @Num int
)
AS
 SET NOCOUNT ON;
 SELECT * FROM dbo.Reply Where Num=@Num

이런 경우에 쉽게 생각 하면 dbo.@TableName 이런 식의 표현이 가능할 줄 알았다.
하지만 이렇게 할 경우 허용이 되지 않는 것을 발견하였다.

그래서 이런 경우 exec()를 사용해서 해당 테이블을 만든 다음에 적용해야 한다.

declare @sql varchar(200)
set @sql='Select * From Reply Where ' + @strSearchField + ' Like ''%' + @strSearchQuery + '%'' Order By Num Desc';
exec(@sql)

하지만 이렇게 했을때 결과는 제대로 나오지만 asp.net에서 데이터 셋을 연동하게 되면
제대로 표현이 안되는 경우가 있다.

하나의 예를 보면..

ALTER PROCEDURE [dbo].[sp_ReplyListNum]
(
 @Num int
)
AS
 SET NOCOUNT ON;
 SELECT * FROM dbo.Reply Where Num=@Num

순수하게 위의 코드를 수행하게 되면  * 부분에서 Reply 의 모든 컬럼을 리턴 시킨다.
그래서 데이터 셋에 서버탐색기를 통해서 저장 프로시저를 드래그앤 드롭을 하게 되면
아래와 같은 내용으로 적용이 된다.

이런 식의 테이블과 테이블 어뎁트가 같이 구성이 된다.

하지만 문제는 exec()를 실행하게 되면 결과는 int 형태로만 나오기 때문에,
테이블 형태로 받아야 할때가 필요 하게 되더라
그래서 찾아보다 보니 sp_executesql 이게 필요하다는 것을 알게 되었다.

아래 부분은 해당 쿼리를 보내고 나서, 보낸 내용중에서 필요한 컬럼만 리턴해 보기 위한 쿼리 내용입니다.

ALTER PROCEDURE [dbo].[sp_ReplyList]
 @TableName varchar(100)
AS
 SET NOCOUNT ON;

 declare @sql nvarchar(2000)
 declare @v_Num int
 declare @v_Name varchar(25)
 declare @v_Email varchar(100)
 declare @v_Title varchar(150)
 declare @v_PostDate datetime
 declare @v_PostIP varchar(15)
 declare @v_Content varchar(1000)
 declare @v_Password varchar(20)
 declare @v_ReadCount int
 declare @v_Encoding varchar(10)
 declare @v_Homepage varchar(100)
 declare @v_ModifyDate datetime
 declare @v_ModifyIP varchar(15)
 declare @v_Ref int
 declare @v_Step int
 declare @v_RefOrder int

 set @sql='Select @v_Num=Num, @v_Name=Name, @v_Email=Email, @v_Title=Title, @v_PostDate=PostDate, @v_PostIP=PostIP, @v_Content=Content, @v_Password=Password, @v_ReadCount=ReadCount, @v_Encoding=Encoding, @v_Homepage=Homepage, @v_ModifyDate=ModifyDate, @v_ModifyIP=ModifyIP, @v_Ref=Ref, @v_Step=Step, @v_RefOrder=RefOrder From ' + @TableName + ' Order by Num Desc'
 --exec(@sql)
 exec sp_executesql
  @sql,
  N'@v_Num int out, @v_Name varchar(25) out,
  @v_Email varchar(100) out, @v_Title varchar(150) out, @v_PostDate datetime out,
  @v_PostIP varchar(15) out, @v_Content text out, @v_Password varchar(20) out,
  @v_ReadCount int out, @v_encoding varchar(10) out, @v_Homepage varchar(100) out,
  @v_ModifyDate datetime out, @v_ModifyIP varchar(15) out,
  @v_Ref int out, @v_Step int out, @v_RefOrder int out',
  @v_Num out,
  @v_Name out,
  @v_Email out,
  @v_Title out,
  @v_PostDate out,
  @v_PostIP out,
  @v_Content out,
  @v_Password out,
  @v_ReadCount out,
  @v_Encoding out,
  @v_Homepage out,
  @v_ModifyDate out,
  @v_ModifyIP out,
  @v_Ref out,
  @v_Step out,
  @v_RefOrder out

-- 위에서 수행한 정보를 select를 통해서 리턴 시켜야 한다.
 select @v_Num as Num,
   @v_Name as Name,
   @v_Email as Email,
   @v_Title as Title,
   @v_PostDate as PostDate,
   @v_PostIP as PostIP,
   @v_Content as Content,
   @v_Password as Password,
   @v_Readcount as ReadCount,
   @v_Encoding as Encoding,
   @v_Homepage as Homepage,
   @v_ModifyDate as ModifyDate,
   @v_ModifyIP as ModifyIP,
   @v_Ref as Ref,
   @v_Step as Step,
   @v_RefOrder as RefOrder