May 15, 2014 at 10:57 am
In my stored procedure, I have a variable I have declared:
@spot_exists int
If I run the following SQL statement, I have no issues and get a value for @spot_exists:
SELECT @spot_exists = COUNT(*) from #return_schedule WHERE spot_id_col1 IS NOT NULL
However, I need to dynamically change the name of the spot_id_col because the actual column name could be spot_id_col1, spot_id_col2, spot_id_col3, etc.
So I have the following code:
SELECT @sql_statement = 'SELECT @spot_exists = COUNT(*) from #return_schedule WHERE ' + @spot_id_col_name + ' IS NOT NULL'
When I look at the value of the @sql_statement , it looks fine, just like this:
SELECT @spot_exists = COUNT(*) from #return_schedule WHERE spot_id_col1 IS NOT NULL
But when I run the procedure, I get an error:
Must declare the scalar variable "@spot_exists".
Can someone tell me what I need to do to fix this issue?
May 15, 2014 at 11:31 am
when you execute dynamic sql, it runs in a different context and cannot see variables declared in the calling context
May 15, 2014 at 11:47 am
OK I think I figured it out now.
May 15, 2014 at 11:48 am
So here's what I did to get the results of the dynamic SQL into a variable for further use.
There might be a better way, but this works.
DECLARE @sql nvarchar(2000),
@C int,
@n varchar(250)
SET @n = 'sys'
SELECT @sql = N'SELECT COUNT(*) FROM sys.objects WHERE name LIKE ''%'+@n+'%'''
CREATE TABLE #temp (counts int)
INSERT INTO #temp (counts)
EXECUTE sp_executesql @sql
SELECT @C = counts FROM #temp
SELECT @C
DROP TABLE #temp
May 15, 2014 at 12:37 pm
You can use OUTPUT with dynamic sql too. No need for a temp table. I took Calvo's example and changed it so it uses OUTPUT parameters. The other change I made is properly parameterize the dynamic sql so it is safe from sql injection.
DECLARE @sql nvarchar(2000),
@C int,
@n varchar(250)
SET @n = 'sys'
SELECT @sql = N'SELECT @MyCountOut = COUNT(*) FROM sys.objects WHERE name LIKE ''%'' + @n + ''%'''
declare @MyCount int
EXECUTE sp_executesql @sql, N'@n varchar(25), @MyCountOut int output', @n, @MyCountOut = @MyCount output
select @MyCount
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 15, 2014 at 1:17 pm
These work great. Thank you all.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply