sql_variant parameters and Dynamic SQL

Let's start off by saying that this article is not a recommendation to use sql_variant as a data type. There are many articles, written by far more reputable people, that have explained why sql_variant isn't a good choice of a data type. Saying that, however, if you are using dynamic SQL then yes you could pass one as a parameter to have that dynamic statement correctly cast that parameter to the correct data type. For a "catch all" query, where you are passing a both a dynamic column and value that could have different data types this permits you to still, in the end, provide a strongly typed value.

Implicit conversion doesn't work

When dealing with a sql_variant, implicit casting is the first problem you need to address. sql_variant has one of the highest precedences for data type. In fact the only data type(s) with a higher precendence are user-defined data types: Data type precedence (Transact-SQL). This is a problem (as will have been outlined in those articles I am sure you have read) as that means that any other value compared will be implicitly cast to a sql_variant unless it's a user-defined data type.

Take this very simple query, assuming that ID is a CLUSTERED PRIMARY KEY:

DECLARE @ID sql_variant;
SET @ID = 90;

SELECT *
FROM dbo.MyTable
WHERE ID = @ID;

This will result in a scan of the table not a seek, meaning that the query is far from likely to be performant. But what about if you have a dynamic statement and you define the parameter being passed to sys.sp_executesql as an int, so that strong typing is maintained, like this (this isn't a dynamic statement but is for demonstration purposes):

DECLARE @ID sql_variant = 90;

EXEC sys.sp_executesql N'SELECT * FROM dbo.MyTable WHERE ID = @ID;', N'@ID int', @ID;

Well that'll get you this:

Implicit conversion from data type sql_variant to int is not allowed. Use the CONVERT function to run this query.

That's right, implicit conversion from a sql_variant to a different data type is not allowed (but yes, implicit conversion to a sql_variant is "A Okay!").

Explicit conversion does work

So what we need to do is explicitly convert the value. So, if we take the above statement and do something like this, it'll work:

DECLARE @ID sql_variant = 90;
DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

SET @SQL = N'DECLARE @ID int = CONVERT(int,@Param)' + @CRLF + @CRLF +
           N'SELECT *' + @CRLF + 
           N'FROM dbo.MyTable' + @CRLF + 
           N'WHERE ID = @ID;';

EXEC sys.sp_executesql @SQL, N'@Param sql_variant', @ID;

This will now work, and it'll be SARGable, so any indexes on the column ID would be used. Great! But does this really solve the problem? Now we have a dynamic statement (again, the above are examples for proof of concept, they aren't really dynamic) but the data type the value is being converted to is hard coded. What can we do about that?

So, as many of you will likely be aware, you can query the sql_variant itself and get its data type, so why don't we get that data type and inject that (safely) into our statement? You can get the underlying data type of the value in a sql_variant with the SQL_VARIANT_PROPERTY function, so would something like this work?

DECLARE @ID sql_variant = 90;
DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

SET @SQL = N'DECLARE @ID ' + QUOTENAME(SQL_VARIANT_PROPERTY(@Param,'BaseType')) + N' = CONVERT(' + QUOTENAME(SQL_VARIANT_PROPERTY(@Param,'BaseType')) + N',@Param)' + @CRLF + @CRLF +
           N'SELECT *' + @CRLF + 
           N'FROM dbo.MyTable' + @CRLF + 
           N'WHERE ID = @ID;';

EXEC sys.sp_executesql @SQL, N'@Param sql_variant', @ID;

Well, for the above, yes but that's because we have a simplistic data type. int doesn't have any additional properties like a length, scale, or precision, and when getting the Base Type property for a sql_variant all you get is the data type's name. This means for something like a varchar, that's going to be a problem. For the DECLARE this will result in the variable being defined as a varchar(1), where as the CONVERT would be defining it as a varchar(30). For the former this is almost certainly going to be wrong, and for the latter it's not unlikely you'd suffer truncation issues. This means you can't just take the sql_variant's base type and "hope for the best", as for many data types the conversion simply isn't going to work correctly. So we'll going to need to get inventive.

A function to define the data type

What the below does is takes a sql_variant value, in this case that'll be your parameter, and then returns a well formatted, and delimit identified, value back which you can (importantly) safely inject into your dynamic statement. This means that a sql_variant can be passed to yor dynamic SQL, however, within the dynamic SQL, it is strongly typed; thus your query is SARGable and your indexes can be used.

I haven't tested this with user defined scalar data types but it should work. Note, however, that SQL_VARIANT_PROPERTY returns the underlying datatype of the scalar type, so if you have a TYPE created from a varchar then SQL_VARIANT_PROPERTY(@SQLVariant,'BaseType') will return the value N'varchar' not the name of your user defined scalar data type. As, however, user defined data types have a higher data type precendence then your input parameter will be implicitly converted, meaning that SARGability would be maintained.

QuoteSqlvariant

CREATE FUNCTION dbo.QuoteSqlvariant (@SQLVariant sql_variant) 
RETURNS nvarchar(258)
AS 
/*
Written by Thom A 2021-03-21
Original Source: https://wp.larnu.uk/sql_variant-and-dynamic-sql/
Licenced under CC BY-ND 4.0
*/
BEGIN
    RETURN QUOTENAME(CONVERT(sysname,SQL_VARIANT_PROPERTY(@SQLVariant,'BaseType'))) +
           CASE WHEN CONVERT(sysname,SQL_VARIANT_PROPERTY(@SQLVariant,'BaseType')) IN (N'char',N'varchar') THEN CONCAT(N'(',CONVERT(int,SQL_VARIANT_PROPERTY(@SQLVariant,'MaxLength')),N')')
                WHEN CONVERT(sysname,SQL_VARIANT_PROPERTY(@SQLVariant,'BaseType')) IN (N'nchar',N'nvarchar') THEN CONCAT(N'(',CONVERT(int,SQL_VARIANT_PROPERTY(@SQLVariant,'MaxLength'))/2,N')')
                WHEN CONVERT(sysname,SQL_VARIANT_PROPERTY(@SQLVariant,'BaseType')) IN (N'datetime2',N'datetimeoffset',N'time') THEN CONCAT(N'(',CONVERT(int,SQL_VARIANT_PROPERTY(@SQLVariant,'Scale')),N')')
                WHEN CONVERT(sysname,SQL_VARIANT_PROPERTY(@SQLVariant,'BaseType')) IN (N'decimal',N'numeric') THEN CONCAT(N'(',CONVERT(int,SQL_VARIANT_PROPERTY(@SQLVariant,'Precision')),N',',CONVERT(int,SQL_VARIANT_PROPERTY(@SQLVariant,'Scale')),N')')
                WHEN CONVERT(sysname,SQL_VARIANT_PROPERTY(@SQLVariant,'BaseType')) IN (N'varbinary') THEN CONCAT(N'(',CONVERT(int,SQL_VARIANT_PROPERTY(@SQLVariant,'TotalBytes'))-4,N')')
                ELSE N''
           END;
END
GO

And then we can update our prior example to use this instead:

DECLARE @ID sql_variant = 90;
DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

SET @SQL = N'DECLARE @ID ' + dbo.QuoteSqlvariant(@ID) + N' = CONVERT(' + dbo.QuoteSqlvariant(@ID) + N',@Param)' + @CRLF + @CRLF +
           N'SELECT *' + @CRLF + 
           N'FROM dbo.MyTable' + @CRLF + 
           N'WHERE ID = @ID;';

EXEC sys.sp_executesql @SQL, N'@Param sql_variant', @ID;

You could also explicitly convert the parameter in the WHERE, which would also be SARGable:

DECLARE @ID sql_variant = 90;
DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

SET @SQL = N'SELECT *' + @CRLF + 
           N'FROM dbo.MyTable' + @CRLF + 
           N'WHERE ID = CONVERT(' + dbo.QuoteSqlvariant(@ID) + N',@ID);';

EXEC sys.sp_executesql @SQL, N'@ID sql_variant', @ID;

Conclusion

As always, I can't recommend the use of a sql_variant, but here I am demonstrating that if you have the really odd requirement of need to have both a dynamic query referencing a dynamic column in the WHERE, and that dynamic column could be a different data type you can use a sql_variant to maintain strong typing through the query. Of course, the likely solution is really that you probably shouldn't be using a dynamic query, and should be rethinking your design.