SQL: Passing different data types to a single stored procedure parameter

There was a recent question on the forums about if there was any way to create a sql parameter with defined type in typescript fashion. What they were after was something like this:

create procedure something
   @myparam 'int' | 'varchar(20)'
as
  ...

They wanted a single parameter that could have different data types, but only from a specific list of data types.

SQL Server doesn’t support that type of construct directly, but what it does do is support the sql_variant data type. So you could have defined the procedure like this instead:

CREATE PROCEDURE something
@MyParam sql_variant
AS
...

That allows you to send in any data type. What’s then remaining to do is to make sure it’s one of the allowed data types. You can do that by querying the SQL_VARIANT_PROPERTY function.

As an example, you can find the underlying data type like this:

DECLARE @DataType sysname = SQL_VARIANT_PROPERTY(@MyParam, 'BaseType');

And then just add logic to check the type name, and if correct, proceed onto casting the values.

2020-01-09