October 26, 2012 at 9:33 am
I'm trying to run a simple dynamic sql statment but am receiving the error below. Do I have to create a stored procedure for this or can I run it in an inline query?
declare @sql varchar(1000)
select @sql = 'select * from table1'
exec @sql
error: Could not find stored procedure 'select * table1'.
October 26, 2012 at 9:39 am
put the variable string in parenthesis:
exec (@sql)
Lowell
October 26, 2012 at 9:54 am
Oh yeah, brackets! Thank you Lowell.
October 26, 2012 at 10:05 am
Make sure you read up and understand sql injection. Dynamic sql like this can be ripe for the picking. You should not execute anything that contains data coming from a user. In other words, if you need to extend this to include a where clause you need to parameterize your dynamic sql.
Let's say that table1 has a column named SomeColumn and you want to find those rows where SomeColumn = 'MySearchVal'. This value is received from the user. We do NOT want to simply execute the dynamic string like the previous example.
In this type of situation you need to proceed a little differently in order to protect your database.
Something like this:
declare @sql nvarchar(1000), @Where nvarchar(100)
set @Where = 'MySearchVal'
select @sql = 'select * from table1 where SomeColumn = @_Where'
exec sp_executesql @sql, N'@_Where nvarchar(100)', @_Where = @Where
_______________________________________________________________
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/
October 29, 2012 at 8:48 am
Thank you for the explanation Sean.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply