SQLTeam.com | Weblogs | Forums

Is it possible to loop through a list of parameters?


I have a stored procedure that has logic to decide if I should add a an item to a kit. To do this I use a command like:

EXEC uspAddSKUToEPID @SKU='ABC_123', @EPID='123456789'

It works, but now I would like to add a list of SKUs to a list. For that I figured that I should wrap that stored procedure within another. IE

EXEC uspAddMultiSKUToEPID @MultiSKU='ABC_123,DEF_456,GHI_789', @current_EPID='123456789'

The problem is that now I need to interpret a list of parameters and loop through them to insert into the first stored procedure. Is this possible in TSQL only or do I need a procedural language to assist?


You can split the @MultiSKU string and loop through the split strings. There are many string splitters available on the web. In particular, I like @JeffModen's code that is described [here]
(http://www.sqlservercentral.com/articles/Tally+Table/72993/). To use the function, copy the code in Fig. 21 of that article to a query window and execute it. That will install the function dbo.DelimitedSplit8K in your database.

A better approach though, is to do the inserts in a single insert statement. When you use the string splitter, you get a virtual table with the SKU's. You can join to that virtual table in your insert statement to do it all in one shot.

Since I don't have your insert statements or logic you are using, I can only show you an example. You can remove the insert portion of the example and run the SELECT portion alone to see what the splitter is doing

DECLARE @MultiSKU VARCHAR(128) ='ABC_123,DEF_456,GHI_789';

INSERT INTO YourDestinationTable


plese incorporate this cte into your SP

;WITH SplitSting AS

    FROM SplitSting
    WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)>0
    FROM SplitSting
    WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)=0


To Split the SKUs and use
select SKU from SplitSting instead of @SKU parameter in stored procedure


Google this problem There are a lot of kludges on this forum. This is because noobs are ignorant of First Normal Form (1NF) -- and pretty much any other Normal Forms,

unfortunately :frowning:

They fall back on the arrays and loops they learned in procedural languages rather than learning to think in RDBMS. After you have read a intro to RDBMS and Normal Forms, then read these articles: