Solved

pass list of items to stored procedure and loop each item

Posted on 2013-12-03
21
1,674 Views
Last Modified: 2013-12-03
Hi

I'm wanting to construct a stored procedure that i can pass in a list of items as a param (my list can have up to 500 items max, all being varchars items of up to 16).

I then want to loop each item and within this loop i update/insert 3 tables for every item.

Im using sql server 2012.

Thanks
0
Comment
Question by:razza_b
  • 8
  • 7
  • 5
  • +1
21 Comments
 
LVL 8

Expert Comment

by:virtuadept
Comment Utility
Use a VARCHAR(MAX) as one of the parameters to store the list. Have whatever is calling the stored procedure build the parameter by concatenating each item with some kind of delimited, such as pipe symbol |.  Then in the proc, parse out the varchar(max) into a list of varchar values. You can then convert them to other types if needed.

Here is a table valued function to do the parsing. You can have this as a user defined function on the database.

CREATE FUNCTION [dbo].[f_split_str_by_delim]
    (
	  @arg_source_str varchar(MAX) = NULL, 
      @arg_delimiter char(1) = ' '
	)
	RETURNS 
		@SPLIT_STR TABLE (id INT IDENTITY, sub_str VARCHAR(MAX))
AS

BEGIN
	DECLARE @curr_str varchar(MAX)
	DECLARE @sub_str varchar(MAX)
	
	SET @curr_str = @arg_source_str
	 
	WHILE Datalength(@curr_str) > 0
	BEGIN
		IF CHARINDEX(@arg_delimiter, @curr_str,1) > 0 
			BEGIN
	           	SET @sub_str = SUBSTRING (@curr_str, 1, CHARINDEX(@arg_delimiter, @curr_str,1) - 1)
	            SET @curr_str = SUBSTRING (@curr_str, CHARINDEX(@arg_delimiter, @curr_str,1) + 1, (Datalength(@curr_str) - CHARINDEX(@arg_delimiter, @curr_str,1) + 1))
				INSERT @SPLIT_STR (sub_str) VALUES (@sub_str)
	   		END
		 ELSE
		 	BEGIN                
				INSERT @SPLIT_STR (sub_str) VALUES (@curr_str)	 		
	           	BREAK;
	       	 END 
	END
	RETURN
END

Open in new window


You can store the results into a table variable in your procedure then loop through it, processing each value as needed.
0
 
LVL 8

Expert Comment

by:virtuadept
Comment Utility
Your procedure would look something like this:

CREATE PROCEDURE p_string_loop_example (@arg_str_list VARCHAR(MAX) NOT NULL )
AS

DECLARE @SPLIT_STR TABLE (id INT IDENTITY, sub_str VARCHAR(MAX))

INSERT @SPLIT_STR
SELECT * FROM f_split_str_by_delim (@arg_str_list, '|')

DECLARE @id INT, @maxid INT
SELECT @id = MIN(id), @maxid = MAX(id)
FROM @SPLIT_STR

WHILE @id <= @maxid
BEGIN
   DECLARE @working_str VARCHAR(MAX)
   SELECT @working_str = sub_str
   FROM @SPLIT_STR
   WHERE id = @id

   -- DO whatever to process @working_str

   SELECT @id += 1  -- works as long as you do not delete rows from @split_str
END

Open in new window

0
 
LVL 1

Author Comment

by:razza_b
Comment Utility
Hi virtuadept

how can i get what you have said to do into my sp, im not sure i follow,,,see attachment...

thanks
SP-to-pass-param-list.txt
0
 
LVL 8

Expert Comment

by:virtuadept
Comment Utility
What is actually calling UpdateStationTransaction?

Does it know how to handle the custom data type StationFSSerialList?

It looks like you are trying to loop through @SNList, which is a table, but you're saying WHILE EXISTS (a row from @SNList) for your loop, and yet you never remove a row after processing it. And also @SNList is being defined as read-only so I'm not sure you could remove a row.

Try just using a loop that is based on the values of SerialItems if they are unique.

SELECT @cur_SerialItem = MIN(SerialItems) from @SNLIST
WHILE @cur_SerilaItem <= (SELECT MAX(SerialItems) FROM @SNList )
BEGIN
   -- your loop logic here

   SELECT @cur_SerialItem = MIN(SerialItems) from @SNLIST
   WHERE SerialItems > @cur_SerialItem
END
0
 
LVL 18

Expert Comment

by:JR2003
Comment Utility
Can just do it by passing in an xml string and converting it into a table.
DECLARE @xml as xml
SET @xml='<root>
<item>Harry</item>
<item>Oliver</item>
<item>Jack</item>
<item>Alfie</item>
<item>Charlie</item>
<item>Thomas</item>
<item>Jacob</item>
<item>James</item>
<item>Joshua</item>
</root>'

SELECT T.c.value('.','varchar(16)') AS item
  INTO #Items
  FROM @xml.nodes('/root/item') T(c)


INSERT INTO Table1
SELECT * 
  FROM #Items

INSERT INTO Table2
SELECT * 
  FROM #Items

INSERT INTO Table3
SELECT * 
  FROM #Items

DROP TABLE #Items

Open in new window

You could use a cursor on the temporary table and loop through it but ideally you would use set based SQL rather than a cursor as illustrated above.
0
 
LVL 1

Author Comment

by:razza_b
Comment Utility
What is actually calling UpdateStationTransaction?
My app will be calling UpdateStationTransaction with the serial list and other params.

Does it know how to handle the custom data type StationFSSerialList?
dont know i have never used this before just trying to find out how all this can be done.

I have created what you have...

1. i have ran this...
CREATE FUNCTION [dbo].[f_split_str_by_delim]
    (
        @arg_source_str varchar(MAX) = NULL,
      @arg_delimiter char(1) = ' '
      )
      RETURNS
            @SPLIT_STR TABLE (id INT IDENTITY, sub_str VARCHAR(MAX))
AS

BEGIN
      DECLARE @curr_str varchar(MAX)
      DECLARE @sub_str varchar(MAX)
      
      SET @curr_str = @arg_source_str
      
      WHILE Datalength(@curr_str) > 0
      BEGIN
            IF CHARINDEX(@arg_delimiter, @curr_str,1) > 0
                  BEGIN
                       SET @sub_str = SUBSTRING (@curr_str, 1, CHARINDEX(@arg_delimiter, @curr_str,1) - 1)
                  SET @curr_str = SUBSTRING (@curr_str, CHARINDEX(@arg_delimiter, @curr_str,1) + 1, (Datalength(@curr_str) - CHARINDEX(@arg_delimiter, @curr_str,1) + 1))
                        INSERT @SPLIT_STR (sub_str) VALUES (@sub_str)
                     END
             ELSE
                   BEGIN                
                        INSERT @SPLIT_STR (sub_str) VALUES (@curr_str)                   
                       BREAK;
                    END
      END
      RETURN
END

2. im trying to run this ...
ALTER PROCEDURE [dbo].[UpdateStationTransaction]
      @arg_str_list VARCHAR(MAX)
      ,@Station          varchar(30)
    ,@Job                 VARCHAR(20)
    ,@QtyComplete       INT
    ,@QtyScrap             INT
    ,@Lot                varchar(30)
      ,@TransCd            varchar(10)
      ,@Remarks            varchar(200)
      ,@Shift                  int
      ,@Operator            varchar(10)
      ,@Qty                  int
      ,@Machine            varchar(4)
      ,@Line                  varchar(4)
      ,@Tester            varchar(5)
      ,@SampleQty            int
      ,@DefectQty            int
      ,@RejCounted      int
      ,@DefCounted      int
      ,@SampleCounted      int
      ,@Reworked            char(3)
      ,@Speed                  varchar(20)
AS

DECLARE @SPLIT_STR TABLE (id INT IDENTITY, sub_str VARCHAR(MAX))

INSERT @SPLIT_STR
SELECT * FROM [MES].[f_split_str_by_delim] (@arg_str_list, '|')

DECLARE @id INT, @maxid INT
SELECT @id = MIN(id), @maxid = MAX(id)
FROM @SPLIT_STR

WHILE @id <= @maxid
BEGIN
   DECLARE @working_str VARCHAR(MAX)
   SELECT @working_str = sub_str
   FROM @SPLIT_STR
   WHERE id = @id

   -- DO whatever to process @working_str

   SELECT @id += 1  -- works as long as you do not delete rows from @split_str
END


just to create a basic construct before i put my 3 statements into it to but i get this error ...

An explicit value for the identity column in table '@SPLIT_STR' can only be specified when a column list is used and IDENTITY_INSERT is ON.
0
 
LVL 1

Author Comment

by:razza_b
Comment Utility
im trying to get me idea from here...http://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure


CREATE TYPE dbo.EmployeeList
AS TABLE
(
  EmployeeID INT
);
GO

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List AS dbo.EmployeeList READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT EmployeeID FROM @List;
END
GO
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
You don't need any function to split a comma separated values into a result set. I suggest you to do this:

1. In your precedure you will have a parameter called, let's say, @param_list varchar(max).
2. You will pass the values in this parameter as a comma separated values: param1,param2,param3,...,paramN
3. Within the stored procedure you will trurn that list in an XML and then use XML query to create a table like paramID, param value like this:

-- values will come as list in a parameter in teh stored procedure
DECLARE 
	@param_list varchar(max)='param1,param2,param3,param4,param5'

-- from here it will be in the stored procedure
-- declare an XML variable to stroe the parameters values
declare
	@xml as XML
-- turn the values list in xml
select 
	@xml='<root><param>'+replace(@param_list,',','</param><param>')+'</param></root>'

-- create a temp table with select .. into to insert the values; drop table if exist first, just in case...
if object_id('tempdb..#params') is not null
	drop table #params
-- insert the values from the XML into #params table as 2 columns: paramID, param
select 
	identity(int,1,1)				as [paramID], 
	P.p.value('.','varchar(16)')	as [param]
into #params
from 
	@xml.nodes('/root/param') P(p)

-- use the #params tabel as you wish
select * from #params

Open in new window

0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
Comment Utility
This is how your stored procedure will look:

ALTER PROCEDURE [dbo].[UpdateStationTransaction]
	 @arg_str_list VARCHAR(MAX) 
	,@Station          varchar(30)
	,@Job                 VARCHAR(20)
	,@QtyComplete       INT
	,@QtyScrap             INT
	,@Lot                varchar(30)
	,@TransCd            varchar(10)
	,@Remarks            varchar(200)
	,@Shift                  int
	,@Operator            varchar(10)
	,@Qty                  int
	,@Machine            varchar(4)
	,@Line                  varchar(4)
	,@Tester            varchar(5)
	,@SampleQty            int
	,@DefectQty            int
	,@RejCounted      int
	,@DefCounted      int
	,@SampleCounted      int
	,@Reworked            char(3)
	,@Speed                  varchar(20)
AS

DECLARE 
	-- declare an XML variable to stroe the parameters values
	@xml as XML,
	@id INT, @maxid INT

-- turn the values list in xml
select 
	@xml='<root><param>'+replace(@arg_str_list,',','</param><param>')+'</param></root>'

-- create a temp table with select .. into to insert the values; drop table if exist first, just in case...
if object_id('tempdb..#params') is not null
	drop table #params
-- insert the values from the XML into #params table as 2 xcolumns: paramID, param
select 
	identity(int,1,1)				as [ID], 
	P.p.value('.','varchar(16)')	as [param]
into #params
from 
	@xml.nodes('/root/param') P(p)
-- get the min (always 1) and max ID values (last identity generated in this procedure)
SELECT 
	@id = 1, 
	@maxid = scope_identity()

WHILE @id <= @maxid
BEGIN
	DECLARE @working_str VARCHAR(MAX)
	SELECT 
		@working_str = [param]
	FROM 
		#params
	WHERE 
		id = @id

   -- DO whatever to process @working_str

	SELECT 
		@id += 1  -- works as long as you do not delete rows from #params
END

Open in new window

0
 
LVL 1

Author Comment

by:razza_b
Comment Utility
i have tried as you suggested Zberteoc, so i have created my sp (see attached) and it ran in successfully, but do you know what dbtype i use from my c# code to pass my list? as my list is a List<string> items
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 1

Author Comment

by:razza_b
Comment Utility
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
SqlString or just String I suppose but I wouldn't be sure. Whatever maps over the varchar type.
0
 
LVL 1

Author Comment

by:razza_b
Comment Utility
would i need to pass my list as a comma seperated string?
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
you should have in your code something like:

.parameter="val1,val2,val2,...,valN"


or, if your values are in some variables already

.parameter= strVariable1+","+strVariable2+","+strVariable3+...,+","+strVariableN
0
 
LVL 1

Author Comment

by:razza_b
Comment Utility
ok, i'll need to loop my List and comma sep them and assign to a string var....


in meantime im just executing the sp with just the one param and trying to print out the values and i get this error...

DECLARE      @return_value int

EXEC      @return_value = [dbo].[UpdateStationTransaction]
            @arg_str_list = N'SBR2011021732320,SBR2011021732321,SBR2011021732322'

SELECT      'Return Value' = @return_value

SELECT INTO failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
0
 
LVL 8

Expert Comment

by:virtuadept
Comment Utility
On the try you did where you were using my first solution take off Identity on the id column on the procedure version of the @SPLIT_STR declare (leave it in the function).
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Add this at the top of your procedure:

SET ANSI_NULLS, QUOTED_IDENTIFIER ON
0
 
LVL 1

Author Comment

by:razza_b
Comment Utility
i got the sp executed i just set the options on and i get this...

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


(3 row(s) affected)
SBR2011021732320
SBR2011021732321
SBR2011021732322

(1 row(s) affected)
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Ok, and? What's the problem? I think you already have all you need.

One mor ething:

also add

SET NOCOUNT ON

at the top of the procedure.
0
 
LVL 8

Expert Comment

by:virtuadept
Comment Utility
This is what is funny to me. I suggested a parameter as a delimited string in the very first answer, which you use in your final solution. And then JR2003 comes along and suggests using XML, which you also use in your solution. And then another expert Zberteoc comes in and combines those two ideas, and helps you debug the minor errors with it, and then he is awared full points for this solution that several people helped solve. Oh well.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
You are right viertuadept. Askers tend to give the points to those who go all the way with their problem, however a split in this case would have been fair.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now