Rules
So, the rules require that I use this picture:
Hopefully those requirements are met. There may be a more romantic way of following the rules, but I’m not very good at either one.
No one has ever accused me of sending flowers.
Waste Management
If you write the good kind of dynamic SQL, that is:
- Parameterized
- Executed with sp_executesql
You’ll probably have run into some silly-ish errors in the practice. Namely, that sp_executesql expects your SQL string and your Parameter string to be NVARCHAR(…).
DECLARE
@sql varchar(MAX) = 'SELECT x = 1;'
EXEC sys.sp_executesql
@sql;
Msg 214, Level 16, State 2, Procedure sys.sp_executesql, Line 1 [Batch Start Line 0]
Procedure expects parameter ‘@statement’ of type ‘ntext/nchar/nvarchar’.
The thing is, if you write complex enough branching dynamic SQL with many paths, you have to:
- Declare the initial variable as nvarchar(probably max)
- Prefix every new string concatenation with N to retain unicodeness
IF @1 = 1 BEGIN SET @sql += N'...' END; IF @2 = 2 BEGIN SET @sql += N'...' END; IF @3 = 3 BEGIN SET @sql += N'...' END; IF @4 = 4 BEGIN SET @sql += N'...' END; IF @5 = 5 BEGIN SET @sql += N'...' END;
And that’s just… tough. If you miss one, your string could go all to the shape of pears. Curiously, the last time I wrote for a T-SQL Tuesday, it was also about dynamic SQL.
So what’s up this time?
Spanning
If you know that no part of your string is going to contain unicode characters that need to be preserved, it is easier to do something like this:
DECLARE
@nsql nvarchar(MAX) = N'',
@vsql varchar(MAX) = 'SELECT x = 1;';
IF @1 = 1 BEGIN SET @sql += '...' END;
IF @2 = 2 BEGIN SET @sql += '...' END;
IF @3 = 3 BEGIN SET @sql += '...' END;
IF @4 = 4 BEGIN SET @sql += '...' END;
IF @5 = 5 BEGIN SET @sql += '...' END;
SET @nsql = @vsql;
EXEC sys.sp_executesql
@nsql;
GO
No worrying about missing an N string prefix, and then set the nvarchar parameter to the value of the varchar string at the end, before executing it.
This can save a lot of time, typing, and debugging.
Concerns
Where you have to be careful is when you may have Unicode characters in identifiers:
DECLARE
@nsql nvarchar(MAX) = N'',
@vsql varchar(MAX) = 'SELECT p = ''アルコール'';'
SET @nsql = @vsql;
EXEC sys.sp_executesql
@nsql;
GO
This will select five question marks. That’s not good. We lost our Unicodeness.
But you are safe when you have them in parameters, as long as you declare them correctly as nvarchar:
DECLARE
@nsql nvarchar(MAX) = N'',
@vsql varchar(MAX) = 'SELECT p = @p;',
@p nvarchar(10) = N'アルコール',
@params nvarchar(MAX) = N'@p nvarchar(10)';
SET @nsql = @vsql;
EXEC sys.sp_executesql
@nsql,
@params,
@p;
GO
This will select アルコール and we’ll all drink happily ever after.
Trunk Nation
One side piece of advice that I would happily give Young Erik, and all of you, is not to rely on data type inheritance to preserve MAX-ness.
As you concatenate strings together, it’s usually a smart idea to keep those strings pumped up:
DECLARE
@nsql nvarchar(MAX) = N'',
@vsql varchar(MAX) = 'SELECT x = 1;';
IF @1 = 1 BEGIN SET @sql += CONVERT(varchar(max), '...') END;
IF @2 = 2 BEGIN SET @sql += CONVERT(varchar(max), '...') END;
IF @3 = 3 BEGIN SET @sql += CONVERT(varchar(max), '...') END;
IF @4 = 4 BEGIN SET @sql += CONVERT(varchar(max), '...') END;
IF @5 = 5 BEGIN SET @sql += CONVERT(varchar(max), '...') END;
SET @nsql = @vsql;
EXEC sys.sp_executesql
@nsql;
GO
And of course, if you need to print out longer strings, I’d recommend Helper_LongPrint, or using the XML processing instruction function to XML-ify things.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
