exec @sql

• Marcus Farrugia

  SSCarpal Tunnel

  Points: 4718

  More actions

  October 26, 2012 at 9:33 am

  #393470

  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'.

• Lowell

  SSC Guru

  Points: 323522

  More actions

  October 26, 2012 at 9:39 am

  #1553264

  put the variable string in parenthesis:

  exec (@sql)

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Marcus Farrugia

  SSCarpal Tunnel

  Points: 4718

  More actions

  October 26, 2012 at 9:54 am

  #1553273

  Oh yeah, brackets! Thank you Lowell.

• Sean Lange

  SSC Guru

  Points: 286602

  More actions

  October 26, 2012 at 10:05 am

  #1553279

  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/

• Marcus Farrugia

  SSCarpal Tunnel

  Points: 4718

  More actions

  October 29, 2012 at 8:48 am

  #1553752

  Thank you for the explanation Sean.