August 4, 2001 at 10:28 pm
I have a requirement for passing a comma-separated list of ID values into a stored procedure (SP) to then break that string apart. Currently, I use VBScript's Split Function on the ASP Page to put the string into an Array and then use a FOR NEXT construct to iterate through each of the array elements and send just a single ID to the SP that inserts into the database.
If a user selects mutiple checkboxes, this means I call the SP multiple times. I would like to pass the set of values to the SP once and inside the SP break the values apart and insert them to the database.
Does any know if there is an equivalent of VBSCript's Split Function in T-SQL?
August 5, 2001 at 9:55 am
there's no equivalent to the split function in Sql
this is what i use
IF LEN(RTRIM(LTRIM(@StrList))) > 0
BEGIN
WHILE PATINDEX('%,%',@StrList) > 0
BEGIN
SET @StrPos = PATINDEX('%,%',@StrList)
SET @TempId = CONVERT(INT,LEFT(@StrList,(@StrPos - 1)))
SET @StrList = RIGHT(@StrList,(LEN(@StrList) - LEN(LEFT(@StrList,@StrPos ))))
INSERT INTO @TempId
VALUES ( @TempId)
END
INSERT INTO @TempId
VALUES ( CONVERT(INT,@StrList))
END
August 5, 2001 at 2:48 pm
Is there a defined limit to the number of params you'd have in the array? Why not just declare them all as parameters...var1, var2, var3, etc.
Another method would be to just insert your values in a table along with an id (uniqueidentifier is perfect for this), then pass the id to the proc and have it read the table.
I know splitting inside the proc saves round trips - but unless performance was killing me I'd just make the iterative calls I think.
Andy
August 5, 2001 at 5:11 pm
Thank you both for you reply. Firstly, GRN thanks for the piece of T-SQL code. And Andy, in answer to your question about a fixed amount of parameters - that's the part of the problem. It can be quite a number. For example, a document has been created and the user has arrived at the final page of the application. This page allows them to assign multiple categories to this document. The categories are stored in a lookup table and so the results of this page are stored in a holding table.
The way it has been resolved now is I open a recordset, iterate through the Array and call the addnew function, then batchupdate. It works fine and so I will not use the T-SQL cod snippet GRN posted but thank you both for your time.
December 8, 2002 at 4:35 pm
There are several scripts on this site that do exactly this. Most of them create and populate a table you can join with your base table on the ID columns entered.
December 9, 2002 at 8:03 am
Try this
http://www.sqlservercentral.com/scripts/contributions/528.asp
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply