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?

Uh, `SELECT @cnt = count(*) from person...` all kinds of things wrong here. Why not `dbo.person`? Why `@cnt` and `@counts`? Why does the case differ? Why no `N` prefix on the 2nd last line? — Aaron Bertrand, Apr 17 '14 at 17:11
I am new to dynamic sql. I borrowed some code from the following question: http://stackoverflow.com/questions/3840730/getting-result-of-dynamic-sql-into-a-variable. I could be completely wrong with this. — w0051977, Apr 17 '14 at 17:14

Solomon Rutzky · Accepted Answer · 2014-04-17T18:04:38.717

There are several things to mention here:

No need to declare the variable used inside of the dynamic sql (i.e. @cnt) outside of it (i.e. at the top)
No need for a cursor as a simple SELECT @var = @var + column construct will concatenate
With no cursor, there is no need to declare the variable used with it (i.e. @Classification)
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.
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

score 0 · Answer 2 · edited May 23 '17 at 12:20

0

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.

edited May 23 '17 at 12:20

Community

1
1

answered Apr 17 '14 at 17:29

Rahul

76,197
13
71
125

what is @retvalOUT? Please make me understand... this @retvalOUT is not declared and still working.. – som_1522 Apr 08 '23 at 17:25

Dynamic SQL Output variable

2 Answers2