Dynamic SQL Output variable

Question

Please see the SQL code below:

declare @Classification as varchar(5)
set @Classification =''
declare @ClassificationSQL as nvarchar(4000)
set @ClassificationSQL=''

declare @cnt int
declare @counts int

DECLARE NicheDeletionOffenderCursor CURSOR FOR  
    select classification from dbnicheoffenderclassificationlookup
    Open NicheDeletionOffenderCursor
    FETCH NEXT FROM NicheDeletionOffenderCursor INTO @Classification

    WHILE @@FETCH_STATUS = 0 
    BEGIN
    If @ClassificationSQL=''
        set @ClassificationSQL='classification like ' + char(39) + '%' + @Classification + '%' + char(39)
    else
        set @ClassificationSQL=@ClassificationSQL + ' OR classification like ' + char(39) + '%' + @Classification + '%' + char(39)
    FETCH NEXT FROM NicheDeletionOffenderCursor INTO @Classification
    END
CLOSE NicheDeletionOffenderCursor 
DEALLOCATE NicheDeletionOffenderCursor 

SET @ClassificationSQL = 'select count(*) as cnt from person where id=903 and (' + @ClassificationSQL + ')' 

EXECUTE sp_executesql @ClassificationSQL, N'@cnt int OUTPUT', @cnt=@Counts OUTPUT

How do I assign the count output from @ClassificationSQL to a variable to use in the next part of the TSQL?

Answer 1

There are several things to mention here:

1. No need to declare the variable used inside of the dynamic sql (i.e. @cnt) outside of it (i.e. at the top)
2. No need for a cursor as a simple SELECT @var = @var + column construct will concatenate
3. With no cursor, there is no need to declare the variable used with it (i.e. @Classification)
4. Single-quotes can be escaped by using two of them (i.e. ''). However, it could simply be preference to use CHAR(39) instead as some people find it to be more readable.
5. Setting a variable in dynamic SQL is just like regular SQL (i.e. SELECT @var = expression FROM...)

End result:

DECLARE @ClassificationSQL NVARCHAR(4000)
DECLARE @Counts INT

SET @ClassificationSQL = COALESCE(@ClassificationSQL + N' OR ', '')
                            + N'classification LIKE ''%'
                            + classification
                            + N'%'''
FROM dbnicheoffenderclassificationlookup

SET @ClassificationSQL =
      N'SELECT @TempCount = COUNT(*) FROM person WHERE id = 903 AND ('
    + @ClassificationSQL
    + N')'

EXECUTE sp_executesql
    @ClassificationSQL,
    N'@TempCount INT OUTPUT',
    @TempCount = @Counts OUTPUT

SELECT @Counts

Answer 2

See a below sample about how you can get the output of a dynamic query (tested in SQL Server 2008 R2). Actual post from where the idea is taken How to get sp_executesql result into a variable?

DECLARE @retval int;   
DECLARE @SQL nvarchar(500);
DECLARE @Param nvarchar(500);

DECLARE @table nvarchar(50)  
SELECT @table = N'newperson'  

SELECT @SQL = N'SELECT @retvalOUT = MAX(salary) FROM ' + @table;  
SET @Param = N'@retvalOUT int OUTPUT';

EXEC sp_executesql @SQL, @Param, @retvalOUT=@retval OUTPUT;

SELECT @retval;

You can make changes to your procedure accordingly.