Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

exec @sql Expand / Collapse
Author
Message
Posted Friday, October 26, 2012 9:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 10:37 AM
Points: 155, Visits: 334
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'.
Post #1377686
Posted Friday, October 26, 2012 9:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 12,906, Visits: 31,984
put the variable string in parenthesis:
exec (@sql)



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1377694
Posted Friday, October 26, 2012 9:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 10:37 AM
Points: 155, Visits: 334
Oh yeah, brackets! Thank you Lowell.
Post #1377704
Posted Friday, October 26, 2012 10:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:13 PM
Points: 13,282, Visits: 12,116
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1377711
Posted Monday, October 29, 2012 8:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 10:37 AM
Points: 155, Visits: 334
Thank you for the explanation Sean.
Post #1378285
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse