Why sp_executesql is giving syntax warning

Question

Question: On the first line, why the following query giving the syntax warning shown in the image below. How can we fix the warning?

DECLARE @USstate nvarchar(15) = N'OHIO'
DECLARE @sql1 nvarchar(500)
SET @sql1 = CONCAT('SELECT * FROM Customer WHERE state = ', @USstate)
EXECUTE sp_executesql @sql1

Screenshot of the syntax warning:

The warning text:

@USstate will be converted to a system.data.sqlclient.sqlparameter object with the following properties: SqlDbType = NVarChar, Size = 15, Precision = 0, SqlValue = OHIO

Although it doesn't appear to have converted it to anything, thats the same datatype you've specified. — Dale K, Apr 07 '23 at 02:14
@DaleK No, it's not failing. Just for this post, I created the sample code that is also giving the warning. The reason I would like to avoid the conversion (shown in the warning) is because my actual code on production server has a `while` loop that loops through thousand of rows - and each row passes an input parameter to `SET @sql1 = CONCAT('SELECT * FROM Customer WHERE state = ', @USstate)`. The warning seems to indicate that a conversion will be applied on each of those thousand iterations before `EXECUTE sp_executesql @sql1` is called. And I'm worried it would cause performance issues. — nam, Apr 07 '23 at 02:22
You're looping through one state at a time and worried a conversion will cause performance issues? This is like having square wheels but worried that regular unleaded isn't powerful enough. Do yourself a favor and revisit why you are processing one state at a time in the first place, and also are you sure that's SSMS giving that warning? Is the column using Always Encrypted? That doesn't look like a normal SSMS IntelliSense error. — Aaron Bertrand, Apr 07 '23 at 02:25
Also `SET @sql1 = CONCAT('SELECT * FROM Customer WHERE state = ', @USstate)` doesn't make sense. If `@USstate` is a string, you'll end up with `WHERE state = OHIO` and unless there's a column named `OHIO` you're going to end up with a syntax error anyway. Also, if your variable is `nvarchar`, then the string literals should be defined as `= N'OHIO'`, and `(N'SELECT...`, not `= 'OHIO'` and `('SELECT...`. Finally, [please terminate your statements](https://sqlblog.org/2009/09/03/ladies-and-gentlemen-start-your-semi-colons). — Aaron Bertrand, Apr 07 '23 at 02:26
@AaronBertrand You may have misunderstood my comment. When I say this is just a sample code, I mean for brevity of this post, I just created a sample scenario. And when I created this sample, it still gives the same warning. But the actual code that will run on production has nothing to do with Customer table or State column. — nam, Apr 07 '23 at 02:40
Do you use SSMS with some plugin? Or some special project type. Haven't seen this kind of syntax validation in normal usage — siggemannen, Apr 07 '23 at 13:11
@siggemannen - It is just an informational message when using always encrypted https://www.sqlservercentral.com/forums/topic/always-encrypted-error-in-sanbox-database — Martin Smith, Apr 08 '23 at 09:47
@MartinSmith, that was an interesting excursion, thanks for the information! — siggemannen, Apr 08 '23 at 18:54

Martin Smith · Accepted Answer · 2023-04-10T14:12:14.067

As stated in the comments the actual code you have given is not SQL injection proof and doesn't make sense.

It isn't needed to see the issue anyway.

Just

DECLARE @USstate nvarchar(15) = N'OHIO'

is sufficient.

You will see this message when the "Enable parameterization for Always Encrypted" option is selected and the connection is also set up to use "Always Encrypted"

The warning is telling you that SSMS will parse the batch and make changes to it (introducing a parameter here) rather than executing exactly what you wrote. e.g. in Profiler I see running the above actually executes...

exec sp_describe_parameter_encryption N'DECLARE @USstate AS NVARCHAR (15) = @p46f1ce96211649f2ba21ae979a9c8b57;

',N'@p46f1ce96211649f2ba21ae979a9c8b57 nvarchar(15)'

exec sp_executesql N'DECLARE @USstate AS NVARCHAR (15) = @p46f1ce96211649f2ba21ae979a9c8b57;

',N'@p46f1ce96211649f2ba21ae979a9c8b57 nvarchar(15)',@p46f1ce96211649f2ba21ae979a9c8b57=N'OHIO'

How can we fix the warning?

Disable this option in SSMS if you don't require it. I would only enable these options if actually working with always encrypted rather than leaving them on all the time as the transformations done to the query text can be problematic.

You nailed it. That was it. Your suggestion resolved the issue. Thank you for sharing your knowledge, wisdom and experience so others can benefit it. I would have not known what was causing the issue. Thank you for nicely explaining it through code, references, and screenshot. Also thank you for the advice regarding proper use of the following option in SSMS: Enable parameterization for Always Encrypted. — nam, Apr 10 '23 at 16:40

Why sp_executesql is giving syntax warning

1 Answers1