Updating records using IN (1, 2, 3) etc.

Question

Post reply

Updating records using IN (1, 2, 3) etc.

barcode-405082

Old Hand

Points: 344
More actions
October 21, 2008 at 8:55 am

#200158

Hi

What must I do to get this stored procedure to work:

CREATE PROCEDURE WO_MARK_AS_PICKED

@WOARRAY AS NVARCHAR(4000)

AS

UPDATE WORKORDERITEMS SET PICKEDID = 2 WHERE WORKORDERITEMID IN (@WOARRAY)

GO

I get an error "Error converting nvarchar to type int". I know I'm getting this 'cause I'm using WOARRAY as a string - it's in the format of 1, 2, 3, 4, 5 etc. to update multiple records. I can always use it like this in classic ASP when I type the stored procedure as a command. What should I do to get this to work as a procedure in SQL 2000?

Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jerry Hung SSChampion Points: 12908 More actions · Answer 1

search for SPLIT functions, or pass in parameters as Arrays

or inline UDF functions that convert a Comma-Separated parameter into a table

quick (but may not be fast) solution is to use something like



 Select  column

 from  table

where  charindex(',' + convert(varchar,WORKORDERITEMID) + ',' , ',' + @WOARRAY  + ',') > 0

SQLServerNewbie MCITP: Database Administrator SQL Server 2005

Jack Corbett SSC Guru Points: 184388 More actions · Answer 2

What SQL Server "sees" as your parameter value is '1,2,3,4' a single string that cannot be converted to an integer. There are 2 ways to handle this situation:

2. Parse the delimited string into a table. There are several ways to do this will probably the fastest using a numbers/tally table explained here[/url] or here[/url].

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

barcode-405082 Old Hand Points: 344 More actions · Answer 3

Thanks Jack and Jerry - I have decided to rather modify the dataset in Visual Studio and submit the changed records back to a webservice that will just update each record at a time using a loop function - not as effecient as your suggested methods, but it's working right now - your solutions are a bit out of my league! :w00t: