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 ina table:

 DROP PROC sp_countGOCREATE PROC [email protected] sysnameASSET NOCOUNT ONDECLARE @intCount int     , @vcSQL varchar(255)SELECT    @vcSQL = " SELECT @intCount = COUNT(*) FROM " + @vcTableEXEC      (@vcSQL)IF @@ERROR = 0     RETURN @intCountELSE     RETURN -1GO

You get:

 Msg 137, Level 15, State 1Must declare variable '@intCount'.NumRows------------1

Change this to:

 DROP PROC sp_countGOCREATE PROC [email protected] sysnameASSET NOCOUNT ONCREATE TABLE #ctr( NumRows int )DECLARE @intCount int     , @vcSQL varchar(255)SELECT    @vcSQL = " INSERT #ctr SELECT COUNT(*) FROM " + @vcTableEXEC      (@vcSQL)IF @@ERROR = 0BEGIN     SELECT    @intCount = NumRows     FROM #ctr     DROP TABLE #ctr     RETURN @intCountENDELSEBEGIN     DROP TABLE #ctr     RETURN -1ENDGODECLARE @intCount intEXEC      @intCount = sp_count sysusersSELECT    @intCount NumRows

And you get:

 NumRows-----------105