sql server 2005 - 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 2005 Forums

Transact-SQL (2005)

sql server 2005

Author

Topic

muvvasiva
Starting Member

14 Posts

Posted - 2009-05-30 : 08:57:15

I am getting error as

"Conversion failed when converting the varchar value 'INSERT INTOEmployees1 VALUES (' to data type int."

CREATE PROCEDURE test 'Employees1',55,'RAMESH',1
@tname varchar(50),
@eid sql_variant,
@name sql_variant,
@reportsto sql_variant
as
begin
DECLARE @table varchar(50)
DECLARE @empid int
DECLARE @lname varchar(50)
DECLARE @repto int

set @table=@tname
set @empid=CONVERT(INT,@eid)
set @lname=CONVERT(varchar(50),@name)
set @repto=CONVERT(INT,@reportsto)
DECLARE @sql varchar(2000)

SET @sql='INSERT INTO'+@table+' VALUES ('+@empid+','+@lname+','+@repto+')'

EXEC (@SQL)

end

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-30 : 09:05:01

SET @sql='INSERT INTO '
+ @table +
' VALUES ('
+ convert(varchar(255),@empid) +
','
+ @lname +
','
+ @repto +
')'

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-30 : 09:11:15

quote:
Originally posted by webfred

SET @sql='INSERT INTO '
+ @table +
' VALUES ('
+ convert(varchar(255),@empid) +
','
+ @lname +
','
+ @repto +
')'

No, you're never too old to Yak'n'Roll if you're too young to die.

It should be

SET @sql='INSERT INTO '
+ @table +
' VALUES ('
+ convert(varchar(255),@empid) +
','''
+ @lname +
''','''
+ @repto +
''')'

Madhivanan

Failing to plan is Planning to fail

muvvasiva
Starting Member

14 Posts

Posted - 2009-05-30 : 09:17:06

Pls explain me ,why should we convert @empid into varchar data type

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-30 : 09:17:20

I thought about my postcounter to wait for the next error

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-30 : 09:18:28

quote:
Originally posted by muvvasiva

Pls explain me ,why should we convert @empid into varchar data type

Otherwise the + sign will try to add the value instead of concatenate

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-30 : 13:54:52

quote:
Originally posted by muvvasiva

Pls explain me ,why should we convert @empid into varchar data type

+ performs addition for numeric value and concatenation for strings. so when it encounters a numeric value (id field) it tries to add it for which other operand must also be numeric. thats why it tries to convert the first part of statment (insert....) to numeric and gives this error

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources