October 21, 2008 at 8:55 am
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
October 21, 2008 at 9:38 am
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
October 21, 2008 at 9:39 am
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
October 22, 2008 at 1:06 am
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:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply