Non-named parameter style for sp_executesql

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

  AlessandroAlpi December 06, 2017 14:13

  - 0 +
• 
  @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.

  buinauskas December 06, 2017 14:34

  - 0 +
• 
  Yes, got it.
  However, you can call sp_executesql with param names:
  

  EXECUTE sys.sp_executesql @stmt = N'SELECT * FROM dbo.Countries WHERE CountryName = @p0;'
    , @params = N'@p0 VARCHAR(100)'
    , @p0 = 'USA';
  

  
  at least, starting from 2008:
  
  
  

  AlessandroAlpi December 06, 2017 15:43

  - 0 +
• 
  @AlessandroAlpi I didn't know that! Should've been more careful and looked at documentation.
  
  You are correct. Thanks a lot!

  buinauskas December 06, 2017 15:45

  - 0 +
• 
  @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

  PDinCA December 06, 2017 20:17

  - 0 +
• 
  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

  krzysztofkroczak December 07, 2017 13:51

  - 0 +
• 

  krzysztofkroczak wrote: »

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

  PDinCA December 07, 2017 19:23

  - 0 +
• 
  @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

  krzysztofkroczak December 08, 2017 09:40

  - 0 +
• 
  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.

  PDinCA December 08, 2017 20:59

  - 0 +

Add comment

Please sign in to leave a comment.