dynamic query error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

dynamic query error

Author

Topic

sqllearner05
Starting Member

9 Posts

Posted - 2012-07-18 : 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

3608 Posts

Posted - 2012-07-18 : 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

9 Posts

Posted - 2012-07-18 : 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

885 Posts

Posted - 2012-07-18 : 05:21:58

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-18 : 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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources