We help IT Professionals succeed at work.
Get Started

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

westmediasystems
on
1,626 Views
Last Modified: 2008-09-18
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
Comment
Watch Question
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE