Can I get an int value returned from EXEC?


DECLARE @count int
DECLARE @cmd nvarchar(500)
SET @cmd = 'SELECT COUNT(*) FROM MyTable WHERE ID  < 0'

Given these variables, how can I get the integer number returned by the SQL in @cmd into @count?
LVL 1
FrancineTaylorAsked:
Who is Participating?
 
MrNeticConnect With a Mentor Commented:
Simple:

DECLARE @count int
DECLARE @cmd nvarchar(500)
SET @cmd = 'SELECT COUNT(*) FROM MyTable WHERE ID  < 0'
EXEC @count = sp_executesql @cmd

0
 
EyalCommented:
SELECT @count = COUNT(*) FROM MyTable WHERE ID  < 0
0
 
FrancineTaylorAuthor Commented:
I need to use the text in the @cmd variable to get the value into @count.  I don't know until I get to this section what is going to be contained in @cmd, only that in int value will be returned
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
EyalCommented:
insert the result to temporary table and query the temporary table
0
 
FrancineTaylorAuthor Commented:
I was hoping to do it without having to create a temporary table.  Never mind, I found my answer:

DECLARE @count int
DECLARE @cmd nvarchar(500)
SET @cmd = 'SELECT COUNT(*) FROM MyTable WHERE ID  < 0'

DECLARE @ParamDef nvarchar(1000), @rtnVal nvarchar(1000)
Select @ParamDef = '@rtnVal nvarchar(1000) OUTPUT'
EXEC dbo.sp_executesql @cmd, @ParamDef, @rtnVal OUTPUT
SET @count = CAST(@rtnVal as int)


However, I'd be happy to accept your answer as a solution if you will include code for it that starts out with my three statements and ends up with @count containing the answer.
0
 
FrancineTaylorAuthor Commented:
Oops, I didn't copy my set @cmd statement quite correctly...the correct code is:

DECLARE @count int
DECLARE @cmd nvarchar(500)
SET @cmd = 'SELECT @rtnVal = COUNT(*) FROM MyTable WHERE ID  < 0'

DECLARE @ParamDef nvarchar(1000), @rtnVal nvarchar(1000)
Select @ParamDef = '@rtnVal nvarchar(1000) OUTPUT'

EXEC dbo.sp_executesql @cmd, @ParamDef, @rtnVal OUTPUT
SET @count = CAST(@rtnVal as int)
0
 
Anthony PerkinsCommented:
Is there any reason you insist on resorting to Dynamic SQL.  You do realize that will mean the user will need SELECT permissions on MyTable, right?  In general, large shops do not typically permit the use of Dynamic SQL due to the inherent risk with SQL Injection and because of the lousy performance it entails.
0
 
John ClaesSenior .Net Consultant & Technical AnalistCommented:
As you don't know the value contained in the @cmd this has a huge chance it won't work
as the value must contain your output variable and it should fill it

I would suggest doing it more Dynamically so you build the Query yourself inside your Procedure.
This has the benefit that :
 * You make the Query so you can make sure it works (Performant)
 * You are sure that it fills your output variable

0
 
MrNeticCommented:
Francine answer is good also, my answer does what you want without changing to much code.
0
 
FrancineTaylorAuthor Commented:
Just what I wanted...thanks!
0
 
FrancineTaylorAuthor Commented:
I have to report that I was mistaken about what the solution was.  Mr Netic's solution seemed to work, but I wasn't paying attention.  When the code is executed, the correct number is output, but it doesn't actually get assigned to @count.  So you need to use my corrected code:

DECLARE @count int
DECLARE @cmd nvarchar(500)
SET @cmd = 'SELECT @rtnVal = COUNT(*) FROM MyTable WHERE ID  < 0'

DECLARE @ParamDef nvarchar(1000), @rtnVal nvarchar(1000)
Select @ParamDef = '@rtnVal nvarchar(1000) OUTPUT'

EXEC dbo.sp_executesql @cmd, @ParamDef, @rtnVal OUTPUT
SET @count = CAST(@rtnVal as int)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.