Conversion failed - 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

General SQL Server Forums

New to SQL Server Programming

Conversion failed

Author

Topic

amodi
Yak Posting Veteran

83 Posts

Posted - 2009-07-01 : 02:45:20

Hello friends,
I have a stored procedure that takes input parameter of type string (@DesignationIds), string parameter may contain a value as '8,12,28'. Complete stored procedure is as follows:

CREATE procedure [dbo].[AddAccessLevel]
@Access_Level_Name varchar(50),
@DesignationIds varchar(50))
As
Insert into Access_levels_Master(Access_level_Name) Values(@Access_level_Name)

Update Designation_Master
SET FKAccessLevel = @@IDENTITY
WHERE Designation_ID in (@DesignationIds)

When the above stored procedure is executed, I am getting the following error in Data Access Layer of the application:

Conversion failed when converting the varchar value '8,12,28' to data type int.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 02:50:10

use CSVTable or fnParseList to convert to result in column

And change the where clause to use IN. Example :


WHERE Designation_ID in (select stringval from CSVTable(@DesignationIds))

quote:
Conversion failed when converting the varchar value '8,12,28' to data type int.

the error is because Designation_ID is an integer data type and SQL Server will attempt to convert the string '8,12,28' into integer which it is not able to do so.

KH
[spoiler]Time is always against us[/spoiler]

amodi
Yak Posting Veteran

83 Posts

Posted - 2009-07-01 : 02:54:48

Thanks Khtan.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-01 : 06:53:33

Also search for Array+SQL Server in google

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-01 : 11:58:21

[code]
CREATE procedure [dbo].[AddAccessLevel]
@Access_Level_Name varchar(50),
@DesignationIds varchar(50))
As
Insert into Access_levels_Master(Access_level_Name) Values(@Access_level_Name)

Update Designation_Master
SET FKAccessLevel = @@IDENTITY
WHERE ',' + @DesignationIds + ',' LIKE '%,' + CAST(Designation_ID AS varchar(10)) + ',%'
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources