dcsimg
Login | Register   
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


Tip of the Day
Language: Enterprise
Expertise: Advanced
May 24, 2000

WEBINAR:

On-Demand

Application Security Testing: An Integral Part of DevOps


Selecting into a Variable Using Dynamic SQL

Asking how to select into a variable using dynamic SQL is a frequent question on newsgroups because SQL Server will not let you select into a variable using the EXEC() function. To get around this, you can create a temporary table and build the SQL dynamically to insert a row into it with the value you want to SELECT into a variable. You can then EXEC() the SQL and SELECT the value into the variable from the temporary table.

For example, suppose you wanted to create a stored procedure that returned the number of rows in a table:

 DROP PROC sp_count
GO

CREATE PROC sp_count
@vcTable sysname
AS
SET NOCOUNT ON
DECLARE @intCount int
     , @vcSQL varchar(255)

SELECT    @vcSQL = " SELECT @intCount = COUNT(*) FROM " + @vcTable
EXEC      (@vcSQL)

IF @@ERROR = 0
     RETURN @intCount
ELSE
     RETURN -1
GO
You get:
 Msg 137, Level 15, State 1
Must declare variable '@intCount'.
NumRows
-----------
-1
Change this to:
 DROP PROC sp_count
GO

CREATE PROC sp_count
@vcTable sysname
AS
SET NOCOUNT ON
CREATE TABLE #ctr
( NumRows int )

DECLARE @intCount int
     , @vcSQL varchar(255)

SELECT    @vcSQL = " INSERT #ctr SELECT COUNT(*) FROM " + @vcTable
EXEC      (@vcSQL)

IF @@ERROR = 0
BEGIN
     SELECT    @intCount = NumRows
     FROM #ctr

     DROP TABLE #ctr
     RETURN @intCount
END
ELSE
BEGIN
     DROP TABLE #ctr
     RETURN -1
END
GO

DECLARE @intCount int
EXEC      @intCount = sp_count sysusers
SELECT    @intCount NumRows
And you get:
 NumRows
-----------
105
David Satz
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date