Non-named parameter style for sp_executesql

buinauskas

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?

AlessandroAlpi

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

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.

buinauskas

@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

@AlessandroAlpi I didn't know that! Should've been more careful and looked at documentation.

You are correct. Thanks a lot!

PDinCA

@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

krzysztofkroczak

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

PDinCA

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)

krzysztofkroczak

@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

PDinCA

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.