How to pass an array of integer values from a table to a stored procedure?

Question

I have a stored proc using dynamic sql that updates a few columns based on the value passed to it. I am trying to test it out for multiple values without having to enter those manually. These values are to be taken from a table. Is there a way to pass all these values in the table and have it go through the proc? Just like in your regular programming language where you would run through an array. I am doing this in sql server 2012.

Code is something like this

CREATE PROCEDURE sp1 @enteredvalue int
AS
BEGIN
  UPDATE table1
  SET column1 = 'some var char value',
      column2 = 'some integer values'
  WHERE xid = @enteredvalue
END

I want to enter the values for that integer parameter (@enteredvalue) from a table that has different values.

Without sample data and sample code, your question seems very vague. I should add that SQL Server doesn't have a concept of arrays, which only makes it less clear. — Gordon Linoff
– Gordon Linoff, Commented Sep 23, 2016 at 0:46
Have you tried using a cursor to go through all the values and call your procedure. — tizzick
– tizzick, Commented Sep 23, 2016 at 1:32

John Cappelletti · Accepted Answer · 2016-09-26 13:19:53Z

2

Perhaps a little more dynamic SQL will do the trick (along with a parser)

Declare @String varchar(max) = '1,25,659'
Declare @SQL varchar(max) = ''
Select @SQL = @SQL + concat('Exec [dbo].[sp1] ',Key_Value,';',char(13))
 From (Select * from [dbo].[udf-Str-Parse-8K](@String,',')) A

Select @SQL
--Exec(@SQL)

Returns

Exec [dbo].[sp1] 1;
Exec [dbo].[sp1] 25;
Exec [dbo].[sp1] 659;

The UDF if needed (super fast!)

CREATE FUNCTION [dbo].[udf-Str-Parse-8K](@String varchar(8000), @Delimiter varchar(50))
Returns Table 
As

--Usage: Select * from [dbo].[udf-Str-Parse-8K]('Dog,Cat,House,Car',',')
--       Select * from [dbo].[udf-Str-Parse-8K]('John||Cappelletti||was||here','||')
--       Select * from [dbo].[udf-Str-Parse-8K]('The quick brown fox',' ')

Return (
   with cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
        cte2(N)   As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 a, cte1 b, cte1 c, cte1 d) A ),
        cte3(N)   As (Select 1 Union All Select t.N+DataLength(@Delimiter) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter)) = @Delimiter),
        cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter,@String,s.N),0)-S.N,8000) From cte3 S)

   Select Key_PS    = Row_Number() over (Order By A.N)
         ,Key_Value = Substring(@String, A.N, A.L) 
         ,Key_Pos   = A.N
   From   cte4 A
)

edited Sep 26, 2016 at 13:19

answered Sep 23, 2016 at 1:32

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

croxfade Over a year ago

I did not follow this exact way but this gave me an idea to generate the exec statements and have those run. Thank you!

John Cappelletti Over a year ago

@croxfade Even better when you build it. Well done. Cheers

Community · Accepted Answer · 2017-05-23 12:06:53Z

1

Another approach is (without Dynamic SQL):

1) Create a new SP where input parameter is a table https://msdn.microsoft.com/en-us/library/bb510489.aspx

2) In that procedure, create a WHILE loop to go through each row and execute your existing SP for each individual row value

Example of WHILE loop is here:

SQL Call Stored Procedure for each Row without using a cursor

edited May 23, 2017 at 12:06

CommunityBot

11 silver badge

answered Sep 23, 2016 at 2:22

Anton

2,8821 gold badge12 silver badges15 bronze badges

Comments

Irawan Soetomo · Accepted Answer · 2016-09-23 07:59:27Z

1

To pass a table into an SP, consider creating a User-Defined Table type. Example:

create type ArrayOfInt as table (IntVal int)
go


create proc SumArray(@IntArray ArrayOfInt readonly)
as
select sum(IntVal) from @IntArray
go

declare @IntArray ArrayOfInt

insert @IntArray values (1), (2), (3)

select * from @IntArray

exec SumArray @IntArray

drop proc SumArray

drop type ArrayOfInt

answered Sep 23, 2016 at 7:59

Irawan Soetomo

1,3251 gold badge15 silver badges35 bronze badges

2 Comments

croxfade Over a year ago

yes but how do I pass this user defined table into my stored proc sp1 that already exists? I tried doing a user defined table but was not able to figure this out. This is all new to me.

Irawan Soetomo Over a year ago

Unfortunately, your sp1 will need to be modified to accept this newly defined User-Defined Table type, to replace the original int parameter.

Collectives™ on Stack Overflow

How to pass an array of integer values from a table to a stored procedure?

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related