exec @sql

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » exec @sql

exec @sql

Rate Topic

Display Mode

Topic Options

Author

Message

Marcus Farrugia

Posted Friday, October 26, 2012 9:33 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 02, 2013 6:08 AM
Points: 139, Visits: 276

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

Lowell

Posted Friday, October 26, 2012 9:39 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 5:28 PM
Points: 11,627, Visits: 27,692

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

Marcus Farrugia

Posted Friday, October 26, 2012 9:54 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 02, 2013 6:08 AM
Points: 139, Visits: 276

Oh yeah, brackets! Thank you Lowell.

Post #1377704

Sean Lange

Posted Friday, October 26, 2012 10:05 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 8,592, Visits: 8,233

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

Post #1377711

Marcus Farrugia

Posted Monday, October 29, 2012 8:48 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 02, 2013 6:08 AM
Points: 139, Visits: 276

Thank you for the explanation Sean.

Post #1378285

« Prev Topic | Next Topic »

Permissions