Post reply

executing dynamic sql string

  • December 5, 2013 at 7:53 am

    #285742

    hi (me again)

    i'm trying to get a proc to execute by passing in a dynamic sql string.

    this works...

    exec [MediaCreateCustomerDataSet] @accountId, @yr, @stockGroup, @stockCode, @stockDescription

    but this doesn't (error: The name '[MediaCreateCustomerDataSet] @accountId, @yr, @stockGroup, @stockCode, @stockDescription' is not a valid identifier.)...

    set @sqlStr = @dataSet + ' @accountId, @yr, @stockGroup, @stockCode, @stockDescription'

    exec @sqlStr

    i also tried (error: Incorrect syntax near ',')...

    set @sqlStr = @dataSet + ' ' + @accountId, @yr, @stockGroup, @stockCode, @stockDescription

    exec @sqlStr

    thanks

  • December 5, 2013 at 2:20 pm

    #1671319

    Why the square brackets around [MediaCreateCustomerDataSet] ?

    They aren't generally needed unless you're using a SQL Server reserved word or have spaces in the name. Have you tried your dynamic query without them?

    Sigerson

    "No pressure, no diamonds." - Thomas Carlyle

  • December 5, 2013 at 2:28 pm

    #1671321

    Can you post the entire statement that is causing the error?


    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • December 5, 2013 at 2:36 pm

    #1671324

    This way be an object name, and optionally parameters after object name

    exec @sqlStr

    THIS way, you can execute a string command: note the parenthesis!

    exec(@sqlStr )

    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!

  • December 6, 2013 at 6:48 am

    #1671504

    Lowell,

    Good catch! Something bothered me about that EXEC statement but I didn't stop to think about it. An idle question for anybody: are those brackets a problem or not?

    Sigerson

    "No pressure, no diamonds." - Thomas Carlyle

  • December 6, 2013 at 6:56 am

    #1671508

    Sigerson (12/6/2013)

    Lowell,are those brackets a problem or not?

    Nope, no problem.

    If you script anything or use the shortcut menu to execute a procedure in SSMS it puts brackets around everything.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • December 6, 2013 at 7:30 am

    #1671522

    the (@sqlStr) seems to have worked, thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply