| Author |
 Topic  |
|
|
sqllearner05
Starting Member
India
9 Posts |
 Posted - 07/18/2012 : 04:53:10
|
Hi,
please help me how to execute below piece of code, it is throwing error "Conversion failed when converting the nvarchar value 'insert into employee(empid,empname) values (' to data type int."
what could be the reason, how can i write dynamic query to another variable
declare @id int
declare @name varchar(20)
declare @query varchar(max)
declare @tablename varchar(20)
set @id = 1
set @name = 'abc'
set @tablename = 'employee'
set @query = N'insert into '+@tablename+'(empid,empname) values ('+@id+','+@name+')'
print @query
sqllearner |
|
|
RickD
Slow But Sure Yak Herding Master
United Kingdom
3560 Posts |
Posted - 07/18/2012 : 04:57:26
|
declare @id int
declare @name varchar(20)
declare @query varchar(max)
declare @tablename varchar(20)
set @id = 1
set @name = 'abc'
set @tablename = 'employee'
set @query = N'insert into '+@tablename+'(empid,empname) values ('+convert(varchar,@id)+','+@name+')'
print @query
|
 |
|
|
sqllearner05
Starting Member
India
9 Posts |
Posted - 07/18/2012 : 05:02:23
|
Thanks RickD
but below query is not inserting in to table
declare @id int
declare @name varchar(20)
declare @query varchar(max)
declare @tablename varchar(20)
set @id = 1
set @name = 'abc'
set @tablename = 'employee'
set @query = N'insert into '+@tablename+'(empid,empname) values ('+convert(varchar,@id)+','+@name+')'
exec(@query)
it is saying invalid column 'abc'
sqllearner |
|
|
|
lionofdezert
Aged Yak Warrior
Pakistan
864 Posts |
Posted - 07/18/2012 : 05:21:58
|
declare @id int
declare @name varchar(20)
declare @query varchar(max)
declare @tablename varchar(20)
set @id = 1
set @name = 'abc'
set @tablename = 'employee'
set @query = N'insert into '+@tablename+'(empid,empname) values ('+convert(varchar,@id)+','''+@name+''')'
exec(@query)
--------------------------
http://connectsql.blogspot.com/ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 07/18/2012 : 10:14:50
|
the easiest way to debug these type of issues is to replace exec with print for print out sql formed first and checking it against syntax before executing it using EXEC
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
dynamic query error
