Foreach update with parameter separated with a delimiter in SQL Server stored procedure

Question

UPDATE SampleTable 
SET Schemaname = @SchemaName,
    SchemaCode = @SchemaCode,
    ForeignKeyColumn = @ForeignKeyColumn,
    IsChildSchema = @IsChildSchema,
    ModifiedBy = @ModifiedBy,
    ModifiedDate = @ModifiedDate
WHERE 
    DataSchemaID = @DataSchemaId

My @ForeignKeyColumn parameter is

2233^SITE_CLM_NUMBER,2236^SITE_ID_N,

Can anyone help me in updating ForeignKeyColumn='SITE_CLM_NUMBER' where DataSchemaID=2233 and ForeignKeyColumn='SITE_ID_N' where DataSchemaID=2236

Just *don't* use such a parameter. If you want to pass multiple values, use a Table-valued parameter and join with the target table — Panagiotis Kanavos, May 29 '17 at 07:32

score 1 · Accepted Answer · answered May 29 '17 at 07:53

It's easy to pass multiple parameter values to a query, using a Table Valued Parameter. These are available in all versions of SQL Server since 2008.

First, you need to create a Table type with the fields you want:

CREATE TYPE dbo.KeyValueType AS TABLE  
( Key int, Value nvarchar(50) )

This allows you to specify a parameter of type KeyValueType with the Key/Value combinations you want, eg @updatedColumns.

You can join the target table with the TVP to update rows with matching DataSchemaID values:

Create Procedure UpdateSchemas(...., @updatedColumns dbo.KeyValueType)

UPDATE SampleTable 
SET                                                                                                     
     Schemaname=@SchemaName                                                                        
    ,SchemaCode=@SchemaCode                                                                                                                                      
    ,ForeignKeyColumn=t.Value
    ,IsChildSchema=@IsChildSchema
    ,ModifiedBy=@ModifiedBy
    ,ModifiedDate=@ModifiedDate                                                                                                                                                                                                              
FROM SampleTable 
    INNER JOIN @updatedColumns t 
    ON t.ID=DataSchemaID

Should I create the TYPE table also in the procedure or outside? @Panagiotis Kanavos — shyam, May 29 '17 at 12:24
I have another problem which I forgot to mention that is I am using Flags for Insert and Update with in the same stored procedure. So can you help me, how do I handle the same while inserting since I will not have the DataSchemaId to join while inserting. @ Panagiotis Kanavos — shyam, May 30 '17 at 09:55
Thanks Kanavas.. your explanation worked but can you please suggest how can I do the same with flags? — shyam, Jun 02 '17 at 12:14
@shyam what do you mean `with flags`? It would be better to post a new question, explain what you want and what you tried. — Panagiotis Kanavos, Jun 02 '17 at 12:43

Marc Guillot · Answer 2 · 2017-05-29T13:41:17.420

-1

You can add an SplitString function, like this one :

How to Split String by Character into Separate Columns in SQL Server

CREATE FUNCTION [dbo].[Split]
(   
 @String varchar(max)
,@Delimiter char
)
RETURNS @Results table
(
 Ordinal int
,StringValue varchar(max)
)
as
begin

    set @String = isnull(@String,'')
    set @Delimiter = isnull(@Delimiter,'')

    declare
     @TempString varchar(max) = @String
    ,@Ordinal int = 0
    ,@CharIndex int = 0

    set @CharIndex = charindex(@Delimiter, @TempString)
    while @CharIndex != 0 begin     
        set @Ordinal += 1       
        insert @Results values
        (
         @Ordinal
        ,substring(@TempString, 0, @CharIndex)
        )       
        set @TempString = substring(@TempString, @CharIndex + 1, len(@TempString) - @CharIndex)     
        set @CharIndex = charindex(@Delimiter, @TempString)
    end

    if @TempString != '' begin
        set @Ordinal += 1 
        insert @Results values
        (
         @Ordinal
        ,@TempString
        )
    end

    return
end

Now you can easily extract each part of your input parameter.

declare @I int;
declare @TMP nvarchar(255);

set @I = 1;
set @TMP = null;
set @TMP = (select StringValue from Split(@ForeignKeyCoumn, ',') where Ordinal = 1); 

while @TMP <> null
begin
  set @ForeignKeyColumn = (select StringValue from Split(@TMP, '^') where Ordinal = 1);
  set @DataSchemaID = (select StringValue from Split(@TMP, '^') where Ordinal = 2);

   -- Update here your table with @ForeignKeyColumn and @DataSchemaID values

   set @I = @I + 1;
   set @TMP = null;
   set @TMP = (select StringValue from Split(@ForeignKeyCoumn, ',') where Ordinal = @I); 
end

PS: If your are using SQL Server 2016 it already includes an SplitString function, so you won't need to add your own. https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql

edited May 29 '17 at 13:41

answered May 29 '17 at 07:29

Marc Guillot

6,090
1
15
42

The OP isn't asking how to split a field value. Even if he did, using a loop is the slowest way to do this – Panagiotis Kanavos May 29 '17 at 07:32
The question is not very clear, but it looks to me that he's asking how to split an input parameter in order to update a tablet with those parts. – Marc Guillot May 29 '17 at 07:34
Even then, a loop is the slowest way to do this. There are other duplicate questions that explain the options. All of them end up proposing the two fastest and easiest methods - XML or SQLCLR – Panagiotis Kanavos May 29 '17 at 07:39
As for splitting an input parameter - why split it at all? Why not provide multiple parameter values? – Panagiotis Kanavos May 29 '17 at 07:40
Yes, it's slow, but this is not a loop over a table, but a loop over a couple of input values, so its cost is negligible but the syntax is very easy to understand (and maintain). About the motive, I have no idea, but I would bet that he can't change it, these must be the values that he gets from other processes. – Marc Guillot May 29 '17 at 07:49
By the way, you are not always able to use table-valued parameters. There are a lot of languages and client libraries that don't support them. – Marc Guillot May 29 '17 at 07:55
It's neither easier nor faster than XML. Anyway, SQL Server already allows you to pass multiple parameter values. Once you have the values, either through splitting or just by passing a TVP, you can *join* with the target table. You don't need handle each value individually. There is no reason to assume the parameter will contain only 2 values – Panagiotis Kanavos May 29 '17 at 07:55
You can. They are available for 10 years now. Any library that doesn't support them should be replaced immediatelly. Most likely it contains more serious errors, eg no support for parameterized queries. Even if you mean FreeTDS, you can replace it with an ODBC driver – Panagiotis Kanavos May 29 '17 at 07:56
And even if you insist on not using a TVP, you can use table-valued constructors to create a table variable on your script, and pass it to the stored procedure. That's how libraries that don't support parameterized queries emulate them. – Panagiotis Kanavos May 29 '17 at 08:02
Look, I'm tired of this. Downvote the answer and get done with it. I for one will wait until the OP explains the motives why he's using those parameters. The performance difference is absolutely negligible, I found XML-based queries much difficult to deal than a simple SplitStriing. And TVP are not an standard SQL feature, so you are wrong, there a lot of languages and libraries that don't support them. In summary, I will leave this answer as it is in order to offer more options to the OP, and I will remove it if he can and decides to change the parameters as you suggest. – Marc Guillot May 29 '17 at 08:06
1

Before you do, check Aaron Bertrands [article on string splitting](https://sqlblog.org/2010/07/07/splitting-a-list-of-integers-another-roundup). There is no reason to use something 20 times slower when there is a well-know good answer. – Panagiotis Kanavos May 29 '17 at 08:10
As for "standard SQL", the question is about SQL Server specifically. So is the looping code posted here. SQL Server 2016, MySql etc actually offer their own string splitting *non-standard* functions like [STRING_SPLIT](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql) – Panagiotis Kanavos May 29 '17 at 08:12
Thanks, very interesting article. I have added a PS, reminding that SQL Server 2016 already includes an SplitString function, as you say. – Marc Guillot May 29 '17 at 08:17

Foreach update with parameter separated with a delimiter in SQL Server stored procedure

2 Answers2