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


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

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