[2k] SP_EXECUTESQL과 임시테이블로 결과값 리턴받기

USE pubs
GO

------------------------------------------------
-- sp_exeutesql 활용
------------------------------------------------
-- CASE 1.
DECLARE @sql NVARCHAR(100)
DECLARE @return_count INT

SET @sql=N'SELECT @count=count(*) FROM sales'

EXEC sp_executesql @sql,N'@count int output',@count=@return_count OUTPUT

SELECT @return_count "리턴 결과값";

-- CASE 2.
declare @sql nvarchar(200), @val nvarchar(10);
declare @ii nvarchar(10), @jj nvarchar(10);

set @sql = N'select @i = ''bo'', @j = ''hyun''';

exec sp_executesql @sql
                 , N'@i nvarchar(10) output, @j nvarchar(10) output'
                 , @i = @ii output, @j = @jj output;
                
select @ii, @jj;

------------------------------------------------
-- 임시테이블 활용
------------------------------------------------
DECLARE @sql VARCHAR(100)
DECLARE @t_count INT
SET @sql='SELECT count(*) as count FROM sales'

CREATE TABLE  #test1 (count int)
INSERT  #test1 EXEC(@sql)

SELECT  count FROM #test1