Cast sql_variant into data_type provided as varchar

I have a following sql table:

 Types table

    ---------------------------------
    |Name (varchar) | Type (varchar)|
    ---------------------------------
    | 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 @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


Source: sql

Leave a Reply