asked on

EXEC sp_executesql @SQL with sql_variant as varchar that inserts into table varchar100

Got a couple of tables called PARAM_NVARCHAR100, PARAM_INT, PARAM_DATETIME. They all have columns "id" and "parameterdata", however the difference is that parameterdata is INT, NVARCHAR100 or DATETIME depending on tablename.

I am now trying to make a procedure that takes a sql_variant and inserts data to relevant table, and so heres the problem when its NVARCHAR100 table: "Implicit conversion from data type sql_variant to nvarchar is not allowed. Use the CONVERT function to run this query."

Anyone has an idea how to solve below sql to make it work?

SET @sql = 'insert into '+@tablename+' (parameterdata) values(@paramvalue) '+ 'set @returnid = SCOPE_IDENTITY()'
EXEC sp_executesql @sql, N'@returnid bigint output, @paramvalue sql_variant',
@returnid output,@paramvalue

westmediasystems

ASKER

Found solution, comments?

if ( cast(SQL_VARIANT_PROPERTY ( @paramvalue,'BaseType') as varchar) = 'varchar' )
or ( cast(SQL_VARIANT_PROPERTY ( @paramvalue,'BaseType') as varchar) = 'nvarchar' )
SET @sql = 'insert into '+@tablename+' (parameterdata) values( cast(@paramvalue as varchar) ) '+
'set @returnid = SCOPE_IDENTITY()'
ELSE
SET @sql = 'insert into '+@tablename+' (parameterdata) values( CONVERT( SQL_VARIANT_PROPERTY ( BaseType, @paramvalue ), @paramvalue) ) '+
'set @returnid = SCOPE_IDENTITY()'

westmediasystems

ASKER

hm, ignore "else" statement... didn't check that one before post! (sigh)

ASKER CERTIFIED SOLUTION

westmediasystems

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial