CODEWITHSUNDEEP
sqlsql-server-2008stored-procedures
PowerStar
PowerStar

Reputation: 895

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

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?

Upvotes: 1

Views: 82

Answers (2)

Mudassir Hasan
Mudassir Hasan

Reputation: 28751

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.

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81970

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,< & >',',')

Upvotes: 2

Related Questions