Dynamically Insert varying number of values based on the input parameter to a procedure

Question

CREATE TABLE #MyTempTable
(
    Name varchar(30)
)

CREATE PROC InsertData_To_TempTable(--Varying number of Names will go here)
AS
BEGIN
    INSERT INTO #MyTempTable(Name) 
    VALUES (--Varying list of values as input parameters from procedure)
END

EXEC InsertData_To_TempTable ('A'),('B') -- one time I may want to insert TWO values

EXEC InsertData_To_TempTable ('A'),('B'),('C') -- other time I may want to insert THREE values

Is there any way that I can dynamically insert varying number of names into my temp table?

either send your values comma separated or consider adding a *User-Defined Table Type* as a parameter in your proc — Stephen, Dec 30 '16 at 12:18
How about just using `insert` rather than a stored procedure? — Gordon Linoff, Dec 30 '16 at 12:19
see detailed answer [here](http://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure) — MtwStark, Dec 30 '16 at 12:20
@GordonLinoff - User will be entering the Names through proc so, wanted it as proc only. Thanks. — PowerStar, Dec 30 '16 at 13:29

score 2 · Answer 1 · answered Dec 30 '16 at 12:21

Without a Split/Parse function

Here we pass the delimiter as a | which can be anything you prefer

Declare @Names varchar(max) = 'Smith, John|Williams, Bill'

Insert Into #MyTempTable(Name)
Select RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
From  (Select x = Cast('<x>'+ replace((Select @Names as [*] For XML Path('')),'|','</x><x>')+'</x>' as xml).query('.')) as A 
Cross Apply x.nodes('x') AS B(i)

With a Split/Parse Function

Declare @Names varchar(max) = 'Smith, John|Williams, Bill'

Insert Into #MyTempTable(Name)
Select RetVal from [dbo].[udf-Str-Parse] (@Names,'|')

The UDF if Needed

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = Row_Number() over (Order By (Select null))
          ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From  (Select x = Cast('<x>'+ replace((Select @String as [*] For XML Path('')),@Delimiter,'</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')
--Select * from [dbo].[udf-Str-Parse]('this,is,<test>,for,< & >',',')

@balaji Happy it helped – John Cappelletti Dec 30 '16 at 12:42 — John Cappelletti, Dec 30 '16 at 12:42

Mudassir Hasan · Answer 2 · 2016-12-30T12:40:58.850

Rather than passing variable number of parameters , send a single comma seperated list of name and inside the stored proc, use a split function to retrieve name values and insert.

CREATE PROC InsertData_To_TempTable
@NameList VARCHAR(MAX)
AS
BEGIN

    INSERT INTO #MyTempTable(Name)
    SELECT Item
    FROM dbo.SplitString(@NameList)
END

And then call

EXEC InsertData_To_TempTable 'A,B,C'

EXEC InsertData_To_TempTable 'A,B,C,D,E'

There is no inbuilt split function so here is one user defined function.

CREATE FUNCTION SplitString
(    
      @Input NVARCHAR(MAX),
      @Character CHAR(1)
)
RETURNS @Output TABLE (
      Item NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT

      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END

      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)

            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END

      RETURN
END
GO

There are other split fucntion with better performance (no use of while loop) that you can search for. SQL Server 2016 has introduced split function. Here is an excellent source for performance analysis of various split functions.

Dynamically Insert varying number of values based on the input parameter to a procedure

2 Answers2