Solved

Can I get an int value returned from EXEC?

Posted on 2011-09-29
11
411 Views
Last Modified: 2012-05-12

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?
0
Comment
Question by:FrancineTaylor
  • 5
  • 2
  • 2
  • +2
11 Comments
 
LVL 15

Expert Comment

by:Eyal
ID: 36818386
SELECT @count = COUNT(*) FROM MyTable WHERE ID  < 0
0
 
LVL 1

Author Comment

by:FrancineTaylor
ID: 36818408
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
 
LVL 15

Expert Comment

by:Eyal
ID: 36818428
insert the result to temporary table and query the temporary table
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 1

Author Comment

by:FrancineTaylor
ID: 36818534
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
 
LVL 1

Author Comment

by:FrancineTaylor
ID: 36818573
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36819026
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
 
LVL 10

Expert Comment

by:John Claes
ID: 36890233
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
 
LVL 5

Accepted Solution

by:
MrNetic earned 500 total points
ID: 36890733
Simple:

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

0
 
LVL 5

Expert Comment

by:MrNetic
ID: 36890744
Francine answer is good also, my answer does what you want without changing to much code.
0
 
LVL 1

Author Closing Comment

by:FrancineTaylor
ID: 36892341
Just what I wanted...thanks!
0
 
LVL 1

Author Comment

by:FrancineTaylor
ID: 36913479
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Loop to go backward 90 days 2 18
sql 2014,  lock limit 5 32
SSRS 2013 - Overlapping reports 2 21
Alternative of IN Clause in SQL Server 3 20
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question