CODEWITHSUNDEEP
sql-servert-sql
Das
Das

Reputation: 59

Issue with splitting values and passing to a stored procedure

Iam getting the below input parameter values in one of the stored procedure.

@DocKey1, @DocValue1, 
@DocKey2, @DocValue2, 
@DocKey3, @DocValue3, 
@DocKey4, @DocValue4, 
@DocKey5, @DocValue5

From this procedure iam calling another procedure to insert each pair of values.

Right now am calling the InsertDocValues stored procedure multiple times to insert each pair.

exec InsertDocValues @DocKey1, @DocValue1
exec InsertDocValues @DocKey2, @DocValue2
exec InsertDocValues @DocKey3, @DocValue3
exec InsertDocValues @DocKey4, @DocValue4
exec InsertDocValues @DocKey5, @DocValue5

Is there anyway i can pass the complete set of values to another procedure as below and then split each pair and insert eg: @DocKey1, @DocValue1 and @DocKey2, @DocValue2 etc

@DocKey1, @DocValue1, @DocKey2, @DocValue2, @DocKey3, @DocValue3, @DocKey4, @DocValue4, @DocKey5, @DocValue5

Below is the procedure am using now to insert 

 Create PROCEDURE [dbo].[InsertDocValues]    
(    
    @DocKey     varchar(20),
    @DocValue   nvarchar(20)
)     
AS       

SET NOCOUNT ON;      

BEGIN       

  INSERT INTO dbo.DocValues(
    DocKey,
    DocValue
  )      
  VALUES(      
    @DocKey,
    @DocValue               
  )      
  End

Please suggest

Upvotes: 0

Views: 75

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81970

I'm getting the sense you have a string of pairs (key,value). Perhaps something like this:

Example

Declare @List varchar(max) = 'Key1:Value1,Key2:Value2'

Insert Into dbo.DocValues(DocKey,DocValue )      
Select DocKey = left(RetVal,charindex(':',RetVal+':')-1)
      ,DocVal = stuff(RetVal,1,charindex(':',RetVal+':'),'')
 From (
        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 replace(@List,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
        Cross Apply x.nodes('x') AS B(i)
      ) A

The Data Inserted would be 

DocKey  DocVal
Key1    Value1
Key2    Value2

Upvotes: 1

Prateek Sharma
Prateek Sharma

Reputation: 132

May be the below code would work for you.

Using user defined table type variable.

CREATE TYPE DocTable AS TABLE   
( 
  DocKey int, 
  DocValue nvarchar(50)
);
GO

And then use this type to create required variable in first SP and pass the same to your second SP.

DECLARE @DocTable AS DocTable;
INSERT INTO @DocTable
SELECT @DocKey1, @DocValue1 UNION ALL
SELECT @DocKey2, @DocValue2 UNION ALL
SELECT @DocKey3, @DocValue3 UNION ALL
SELECT @DocKey4, @DocValue4 UNION ALL
SELECT @DocKey5, @DocValue5

You can create the above insert query dynamically also. There are so many ways to populate a table. So, use any one as you are getting output from your first SP.

And then call your Second SP.

EXEC [dbo].[InsertDocValues] @DocTable

Changes in second SP would be look like this.

Create PROCEDURE [dbo].[InsertDocValues] 
(
    @DocTable DocTable READONLY
)
AS       
SET NOCOUNT ON;
BEGIN       
    INSERT INTO dbo.DocValues(
        DocKey,
        DocValue
    )      
    SELECT 
        DocKey,
        DocValue 
    FROM @DocTable     
END

Upvotes: 2

Related Questions