Is it possible to loop through a list of parameters?

1

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?

2

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
	(SKU)
SELECT
	Item 
FROM
	dbo.DelimitedSplit8K(@MultiSKU,',')
3

plese incorporate this cte into your SP

;WITH SplitSting AS
(
SELECT
LEFT(@SKU,CHARINDEX(',',@SKU)-1) AS SKU
,RIGHT(@SKU,LEN(@SKU)-CHARINDEX(',',@SKU)) AS Remainder

UNION ALL
SELECT
    LEFT(Remainder,CHARINDEX(',',Remainder)-1)
        ,RIGHT(Remainder,LEN(Remainder)-CHARINDEX(',',Remainder))
    FROM SplitSting
    WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)>0
UNION ALL
SELECT
    Remainder,null
    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

4

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: