CODEWITHSUNDEEP
sql-serverstored-proceduressql-update
Bharat Nanwani
Bharat Nanwani

Reputation: 703

Last Entry in Stored Procedure does not Update

I have written below stored procedure, it works well, however, it does not update the last entry in string. Any ideas why?

create procedure testProceduretwo 
    @vendorIds varchar(max)
as
    declare @pos int
    declare @vendor varchar(50)
    set @pos = charindex(',', @vendorIds)

    while @pos <> 0
    begin
        set @vendor = left(@vendorIds, @pos-1)
        exec ('update DS_REGISTERED_VENDOR set ppu_flag=''Y'' where Vendor_Id = ' + @vendor + '')
        print 'updated vendor id - ' + @vendor

        set @vendorIds = stuff(@vendorIds, 1, @pos, NULL)
        set @pos = charindex(',', @vendorIds)
    end

    exec ('update DS_REGISTERED_VENDOR set ppu_flag=''Y'' where Vendor_Id = ' +  @vendor + '')

I call it using this:

exec testProceduretwo '00072211,00000004,00120188,00000001'

It does not update the value for - 00000001 (last entry)

Upvotes: 0

Views: 28

Answers (2)

Thom A
Thom A

Reputation: 95658

You could rewrite this to be far safer, and quicker by doing this:

CREATE PROC testProceduretwo @vendorIds varchar(max) AS
BEGIN

    UPDATE DS
    SET ppu_flag = 'Y'
    FROM DS_REGISTERED_VENDOR DS
         CROSS APPLY STRING_SPLIT(@vendorIds,',') SS --Assumes SQL Server 2016+
    WHERE SS.[value] = DS.Vendor_Id;
END
GO

If you aren't using SQL Server 2016 then you can use an XML Splitter, or delimitedsplit8k(_lead). A quick google will find either of these.

Upvotes: 2

George Menoutis
George Menoutis

Reputation: 7240

...Because you use charindex

set @pos = charindex(',', @vendorIds)

to find the first comma, and then take everything that is before it (left):

set @vendor = left(@vendorIds, @pos-1)

The fastest way would be to manually add a comma at procedure start:

(......)
declare @pos int
set @vendorIds+=',' -- add this row
declare @vendor varchar(50)
(......)

This is just a functional reasoning, consider Larnu's warnings too.

Upvotes: 0

Related Questions