How do I get the return value from a CASE statement as a dynamic SQL query?

Question

DECLARE @command = 'SELECT  CASE WHEN ( SELECT  COUNT(*)
                    FROM    [table] WITH ( NOLOCK )
                    WHERE   DATEDIFF(minute, systemupdatedtimestamp, GETDATE()) < 10
                  ) > 0 THEN 0
             ELSE 1
        END'

Now I need to get the 'return value' of the above command (0 or 1).

EXEC(@commnad)

How do I get the return value from the above?

I tried

SET @ReturnValue = EXEC(@command)

but no luck.

possible duplicate of [How to get sp_executesql result into a variable?](http://stackoverflow.com/questions/803211/how-to-get-sp-executesql-result-into-a-variable) — LittleBobbyTables - Au Revoir, Sep 18 '12 at 16:55

kristof · Accepted Answer · 2012-09-19T15:07:18.027

use sp_executesql

in your case it is something like:

declare @myOut bit
declare @SQLString nvarchar(500)
set @SQLString = N'SELECT @myOutValue = CASE WHEN ( SELECT  COUNT(*)
                    FROM    [table] WITH ( NOLOCK )
                    WHERE   DATEDIFF(minute, systemupdatedtimestamp, GETDATE()) < 10
                  ) > 0 THEN 0
             ELSE 1
        END'

declare @ParmDefinition nvarchar(500)
set @ParmDefinition = N'@myOutValue bit OUTPUT'


EXECUTE sp_executesql @SQLString, @ParmDefinition, @myOutValue = @myOut OUTPUT
select @myOut

Update:

To follow up on you comment. If you do not want to modify the original string containing your sql command (e.g. it is used in other places etc) you can wrap that command inside a new string something like:

@SQLString = 'select @myOutValue = (' + @yourOrigSqlCommand + ')'

And call sp_executesql in the same way as above.

what if I cannot change the SQLString itself? – JJ. Sep 18 '12 at 17:10 — JJ., Sep 18 '12 at 17:10
@Testifier youy could wrap in inside a new string then. – kristof Sep 19 '12 at 08:33 — kristof, Sep 19 '12 at 08:33

score 0 · Answer 2 · answered Sep 18 '12 at 17:32

0

I believe this solves your problem.

DECLARE @command nvarchar(max) = 'SELECT  CASE WHEN ( SELECT  COUNT(*)
                    FROM    [table] WITH ( NOLOCK )
                    WHERE   DATEDIFF(minute, systemupdatedtimestamp, GETDATE()) < 10
                  ) > 0 THEN 0
             ELSE 1
        END'

exec sp_executesql @command

answered Sep 18 '12 at 17:32

Sasa Popovic

53
1
6

Sasa, how do I get the case value when I do the EXEC ?? from the query I run, I need to set the CASE VALUE (1 or 0) to a variable. – JJ. Sep 18 '12 at 17:34

How do I get the return value from a CASE statement as a dynamic SQL query?

2 Answers2