[RESOLVED] Pass array to SQL Stored Procedure

[dbasnett]

I have a list of id's that I want to pass to a stored procedure and have that procedure find those id's. I need help on passing the list and the stored procedure.
Thanks.

[techgnome]

Two ways... one I've done and the other I've seen but never personally done...

First the "easy" way... pass in the list as a CSV string... the loop and parse the data and put it into a temp table or a table var.... then treat it like a table.

Second way... this I've really only seen done from SProc to SProc and not called externally so I'm not sure how it would work or if it can... but create a User-Defined Table Type... you can then pass it in as a parameter as that type and it gets treated like a table.

Yet one more... a combination of the two. At my last job, we had a few generic table types... one that allowed just values, another that allowed key/value pairs, and several others... each of which also had a splitter function... so you could pass the splitter your data (assuming it was in the correct format) and it would return the requested User Table Type with the table filled with the values. From there you could select from it, or pass it to another SProc, or what ever.


-tg

[GaryMazzone]

In SQL Server 2016 and newer (that means Azure also) there is string_split function that you can use.... I pass a comma deliminated string as a var use
Create table #tempTable (id int);
INSERT into #tempTable
SELECT CAST([value] as INT) from STRING_SPLIT(@varName,',')

the use that temptable as an inner join

[techgnome]

Cool! It's nice to see they finally added a string split function to SQL Server...


-tg

[GaryMazzone]

If you are on a version below 2016 then you can use this to split the strings

CREATE FUNCTION usp_fnsplit (
	@inputVals VARCHAR(MAX),
	@delimiter CHAR(1)
)
RETURNS @output TABLE (splitval VARCHAR(MAX))
BEGIN
	DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @inputVals) 
    WHILE @start < LEN(@inputVals) + 1 
		BEGIN 
			IF @end = 0  
				SET @end = LEN(@inputVals) + 1
       
			INSERT INTO @output (splitval)  
			VALUES(SUBSTRING(@inputVals, @start, @end - @start)) 
			SET @start = @end + 1 
			SET @end = CHARINDEX(@delimiter, @inputVals, @start)
        
		END 
	RETURN
END

[dbasnett]

If you are on a version below 2016 then you can use this to split the strings

CREATE FUNCTION usp_fnsplit (
	@inputVals VARCHAR(MAX),
	@delimiter CHAR(1)
)
RETURNS @output TABLE (splitval VARCHAR(MAX))
BEGIN
	DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @inputVals) 
    WHILE @start < LEN(@inputVals) + 1 
		BEGIN 
			IF @end = 0  
				SET @end = LEN(@inputVals) + 1
       
			INSERT INTO @output (splitval)  
			VALUES(SUBSTRING(@inputVals, @start, @end - @start)) 
			SET @start = @end + 1 
			SET @end = CHARINDEX(@delimiter, @inputVals, @start)
        
		END 
	RETURN
END

I'm not good with SQL. I am creating a stored procedure and don't know how to integrate the above into that.

What I have is

CREATE PROCEDURE [revision].[XNIDList] 
	-- Add the parameters for the stored procedure here
	<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
	<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS

BEGIN
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END

which is what you get when you right click Stored Procedures / New Stored Procedure...

[dbasnett]

I ended up creating a string of numbers surrounded by ';'. It looks like this
;123;;456;;789;
They are passed to the SPROC as NIDList
The SPROC looks like this

	@NIDList VARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;
	SELECT [NID]
		  ,[P1]
		  ,[P2]
		  ,[RC]
		  ,[Active]
		  ,[Flags]
		  ,[Hist]
	  FROM [SectNum] 
	  WHERE @NIDList like '%;'+cast([NID] as VARCHAR(MAX))+';%';

There are approximately 50,000 records in the table and if I retrieve 300 it takes a couple of seconds. Not great but acceptable.

[GaryMazzone]

The function is a table valued function... it is compiled into the DB.

To all it something like this:
Declare @NIDList VARCHAR(MAX) = '123;456;789'
SELECT [NID]
,[P1]
,[P2]
,[RC]
,[Active]
,[Flags]
,[Hist]
FROM [SectNum] SN
Inner JOIN usp_fnsplit (@NIDList ,';') slp
on SN.NID = slp.value

[dbasnett]

Thanks for your help. I ended up with a table-valued function

ALTER FUNCTION [NID_Split] (
	@inputVals VARCHAR(MAX),
	@delimiter CHAR(1)
)
RETURNS @output TABLE (splitval int)
BEGIN
	DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @inputVals) 
    WHILE @start < LEN(@inputVals) + 1 
		BEGIN 
			IF @end = 0  
				SET @end = LEN(@inputVals) + 1
       
			INSERT INTO @output (splitval)  
						VALUES(CAST(SUBSTRING(@inputVals, @start, @end - @start)as int)) 

			SET @start = @end + 1 
			SET @end = CHARINDEX(@delimiter, @inputVals, @start)
		END 
	RETURN
END

Then modified the SPROC

ALTER PROCEDURE [XNIDList] 
	@NIDList VARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;
		SELECT [NID]
			  ,[P1]
			  ,[P2]
			  ,[RC]
			  ,[Active]
			  ,[Flags]
			  ,[Hist]
			  FROM [NID_Split](@NIDList,';') as lsn
				INNER JOIN [SectNum] on lsn.splitval=[NID];
  END

This sped things up dramatically! It went from seconds to under a tenth of a second.

[GaryMazzone]

I would make the return a varchar(1000), (RETURNS @output TABLE (splitval int)). That way you can use it for things other than just integers

[dbasnett]

I would make the return a varchar(1000), (RETURNS @output TABLE (splitval int)). That way you can use it for things other than just integers

I considered that. For now all that it is used for is ID fields. Now that I know what to do I think I would create a different function for strings.

[GaryMazzone]

Remember if you are on SQL Server 2016 or better there is already a function from MS to do this named STRING_SPLIT()