dcsimg
Results 1 to 12 of 12

Thread: [RESOLVED] Pass array to SQL Stored Procedure

  1. #1

    Thread Starter
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Pointless Forest 38.517,-92.023
    Posts
    9,214

    Resolved [RESOLVED] Pass array to SQL Stored Procedure

    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.
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,410

    Re: Pass array to SQL Stored Procedure

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    #28 for the Yanks coming GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,334

    Re: Pass array to SQL Stored Procedure

    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
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,410

    Re: Pass array to SQL Stored Procedure

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


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5
    #28 for the Yanks coming GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,334

    Re: Pass array to SQL Stored Procedure

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

    Code:
    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
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  6. #6

    Thread Starter
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Pointless Forest 38.517,-92.023
    Posts
    9,214

    Re: Pass array to SQL Stored Procedure

    Quote Originally Posted by GaryMazzone View Post
    If you are on a version below 2016 then you can use this to split the strings

    Code:
    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
    Code:
    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...
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

  7. #7

    Thread Starter
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Pointless Forest 38.517,-92.023
    Posts
    9,214

    Re: Pass array to SQL Stored Procedure

    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
    Code:
    	@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.
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

  8. #8
    #28 for the Yanks coming GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,334

    Re: Pass array to SQL Stored Procedure

    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
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  9. #9

    Thread Starter
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Pointless Forest 38.517,-92.023
    Posts
    9,214

    Re: Pass array to SQL Stored Procedure

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

    Code:
    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

    Code:
    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.
    Last edited by dbasnett; Aug 12th, 2019 at 02:53 PM.
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

  10. #10
    #28 for the Yanks coming GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,334

    Re: [RESOLVED] Pass array to SQL Stored Procedure

    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
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  11. #11

    Thread Starter
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Pointless Forest 38.517,-92.023
    Posts
    9,214

    Re: [RESOLVED] Pass array to SQL Stored Procedure

    Quote Originally Posted by GaryMazzone View Post
    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.
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

  12. #12
    #28 for the Yanks coming GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,334

    Re: [RESOLVED] Pass array to SQL Stored Procedure

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

    Mazz1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width