exec @sql

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

  • 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!

  • Oh yeah, brackets! Thank you Lowell.

  • 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/

  • 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