Cast sql_variant into data_type provided as varchar

Question

I have a following sql table:

 Types table

    --------------------------------------
    |Name(varchar(50))|Type (varchar(50))|
    --------------------------------------
    | Car             | varchar(50)      |
    | Apples          | int              |
    --------------------------------------

I am using another tables for storing values such as:

Apples table:

    ----------------------------
    |Value (providedType - int)|
    ----------------------------
    |50                        |
    |60                        |
    ----------------------------

To insert values into these tables I am using a stored procedure (part of it):

CREATE PROCEDURE [dbo].[AddValue]
@value sql_variant
@name varchar(50)
@tableName (50)
AS
BEGIN

DECLARE @Sql NVARCHAR(MAX)
DECLARE @valueType VARCHAR(50)
SET @valueType = (SELECT [Type] FROM [dbo].[Types] WHERE [Name] = @name)

SET @Sql = N'INSERT INTO [dbo].'+ @tableName + N' VALUES(' + @value + N')'
EXECUTE sp_executesql @Sql 
...

Dynamic execute will throw an exception that implicit casting of sql_variant is not allowed. Is there any way to convert sql_variant type into the type that is provided as varchar? Such as:

CONVERT(@valueType, @value)

Where @valueType is varchar not datetype

Solution

Yes, you can pass sql_variants as parameters to sp_executesql, but you'll need to continue down the dynamic SQL route with the "Cast to" type, and use the name of the Type that you've determined for the column to be used in a CAST.

Take this for example:

CREATE TABLE Foo
(
    ID INT
);
declare @type NVARCHAR(20) = N'INT'; -- Substitute your Type here.
declare @tableName NVARCHAR(50) = 'Foo';
declare @value sql_variant;
set @value = 1234;
DECLARE @Sql AS NVARCHAR(MAX) = N'INSERT INTO [dbo].'+ @tableName +
           N' VALUES(CAST(@value AS ' + @type + '))';
EXECUTE sp_executesql @Sql, N'@value sql_variant', @value = @value;  

Needless to say, you'll need to ensure that your @tableName and Type data will need to be run against a whitelist, in order to protect against Sql Injection vulnerabilities with dynamic Sql like this.

SqlFiddle here

Comments

[Martin Ch]

Works great! Thanks! I am using QUOTENAME for table names and types as well