What are the challenges you face when working across database platforms? Take the survey
Options

Non-named parameter style for sp_executesql

I've got a perfectly valid sp_executesql statement to run my dynamic queries, however SQL Prompt marks it as an issue. There's no way to add parameter names to it.

Code sample:
EXECUTE sys.sp_executesql N'SELECT * FROM dbo.Country WHERE CountryName = @p0;'
  , N'@p0 VARCHAR(100)'
  , @p0 = 'USA';

Should I simply ignore it or Red Gate would be able to fix it?

Best Answer

Answers

  • Options
    hay @buinauskas,
    I guess you're speaking about SQL Prompt 9 Rules. You can uncheck the following rule in order to avoid the "issue". It's up to you to manage what rules you'd like to consider. It's not a bug "to be fixed" IMHO.
    lws4rb8c2r3q.png
    Alessandro Alpi
    CTO @ Engage IT Services s.r.l.
    Database Administrator and Team Leader
    Microsoft MVP - Data Platform
    Staff member of getlatestversion.it community
    personal website | blog
  • Options
    buinauskasbuinauskas Posts: 10 Bronze 1
    @AlessandroAlpi hello!

    I know I can ignore this rule just by excluding it. However that's not what I'd like to have.

    User stored procedures can and should be executed in named parameter style. That makes code more readable and easier to understand.

    However some built in (system) stored procedures can only be executed ONLY IN non-named parameter style (as far as I know). One of them is sys.sp_executesql.

    So it kinda makes sense not to label these kind of procedures as code analysis violations because there's no way to execute it in named parameter style.

    Also, this is not a deal breaker, but something rather nice to have.

    Hope I'm clearer this time.
  • Options
    buinauskasbuinauskas Posts: 10 Bronze 1
    @AlessandroAlpi I didn't know that! Should've been more careful and looked at documentation.

    You are correct. Thanks a lot! :smile:
  • Options
    PDinCAPDinCA Posts: 642 Silver 1
    @buinauskas Appears RG started to give us this "Unwrap" sp_executesql ability, but it looks like it stalled back in November 2016 in v7.3. PITY! V9 doesn't respond to the Beta's Ctrl+B+I combo.

    Maybe a few more up-votes might kick-start it...
    https://redgate.uservoice.com/forums/94413-sql-prompt/suggestions/4013363-unwrap-sp-executesql
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
  • Options
    Hi @PDinCA,

    It is still possible to inline execute statement in your script. You can do it from the context menu (right mouse click "Inline EXEC") and the shortcut [CTRL+B, CTRL+I] is still available.
    Do you have some issues with this shortcut?

    Best regards,
    Krzysztof
  • Options
    PDinCAPDinCA Posts: 642 Silver 1
    edited December 7, 2017 7:34PM
    Hi @PDinCA,

    It is still possible to inline execute statement in your script. You can do it from the context menu (right mouse click "Inline EXEC") and the shortcut [CTRL+B, CTRL+I] is still available.
    Do you have some issues with this shortcut?

    Best regards,
    Krzysztof

    @Krzysztof - Yes, v9.0.2.3223 in use. Opened new Query Window. Typed sp_executesql. Tried CTRL+B, CTRL+I and nothing. Highlighted, repeat keys - nothing. Added EXEC, repeat keys - nothing. Right-click context menu features "Inline EXEC" but SQL Prompt Errors on using it- see attached image.k2mo13pr828t.png

    Made it executable:
    DECLARE @SQL nvarchar(4000);
    
    EXEC sys.sp_executesql @SQL
    

    No dice, not even an error this time. Maybe I'm just using the feature wrongly, as it isn't in a procedure...? (There's no shortcut key-combo on the right-click menu, BTW)
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
  • Options
    @PDinCA sp_executesql is stored procedure which executes string as query
    so your script should look like that:
    EXECUTE sys.sp_executesql @stmt = N'SELECT * FROM dbo.Countries WHERE CountryName = ''CountryName'';'
    
    Now you shouldn't have the error. I agree that the error dialogue is not verbose and doesn't say what's wrong. We will work on that in near future.
    If you would like to use some params in that sp you can do it like AlessandroAlpi mentioned above:
    EXECUTE sys.sp_executesql @stmt = N'SELECT * FROM dbo.Countries WHERE CountryName = ss;'
    , @params = N'@p0 VARCHAR(100)'
      , @p0 = 'USA';
    

    Hope that helps
  • Options
    PDinCAPDinCA Posts: 642 Silver 1
    Actually, my preferred behavior would be to have it treated like an exec of any user procedure - expand the signature when I tab after the last letter of sp_executesql

    Well aware of the other options re scripting - have only been using the sp for 13 years!

    A snippet will do in lieu of preferred behavior and fiddling around to try and get the Ctrl+ keys or right-click-blows-up.

    Signing off this thread. Cheers.
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
Sign In or Register to comment.