Conversion failed when converting the varchar value to data type int.

I have a web application that defines the list of order numbers that I want to update in a DB. This list of order numbers (which is defined as int in the DB) is collected in a string. I would like to pass the value of this string to the sql parameter to update the DB, but when I run it I get the error message: "Conversion failed when converting the varchar value ''44','45'' to data type int."

This code below represents the situation:
declare @OrderSelection varchar(max)
set @OrderSelection = '''44'',''45'''
update SY1_OrderDetail set OrderLineStatus = 1
where orderNumber in (@OrderSelection)

Please, could you help me to solve the problem?
declare @OrderSelection varchar(max)
set @OrderSelection = '''44'',''45'''
update SY1_OrderDetail set OrderLineStatus = 1
where orderNumber in (@OrderSelection)

Open in new window

EdsSystemAsked:
Who is Participating?
 
BrandonGalderisiConnect With a Mentor Commented:
You have to put it in dynamic SQL in order to pass @OrderSelection in like that.  Or, use a UDF to split the @OrderSelection into a table and join to it or us in/exists.

Here's the function I wrote to do it:
http://sqlservernation.com/blogs/howtos/archive/2009/03/07/converting-a-delimited-string-into-a-table.aspx



--with dynamic sql
declare @OrderSelection varchar(max)
set @OrderSelection = '44,45'
 
exec('update SY1_OrderDetail set OrderLineStatus = 1 where orderNumber in (' + @OrderSelection + ')')
 
 
--with function 1
declare @OrderSelection varchar(max)
set @OrderSelection = '44,45'
 
update SY1_OrderDetail set OrderLineStatus = 1 where orderNumber in
(select cast(theValue as int) from dbo.fn_delimitedToString(@OrderSelection,','))
 
--with function 2
declare @OrderSelection varchar(max)
set @OrderSelection = '44,45'
 
update sy 
set OrderLineStatus = 1 
from SY1_OrderDetail sy
where exists
(select 1 from dbo.fn_delimitedToString(@OrderSelection,',') where cast(theValue as int) = sy.OrderNumber)

Open in new window

0
 
momi_sabagCommented:
that won't work
is this a one time thing or do you need to run this in a  procedure>?
0
 
EdsSystemAuthor Commented:
I will use it in a procedure.
0
 
EdsSystemAuthor Commented:
Thanks a lot for your prompt response!!!
0
All Courses

From novice to tech pro — start learning today.