Update multiple records with one shot

It passed sometime when I made the last post with stored procedures. Probably the problem is that I'm using a lot EF, LINQ2SQL and other ORMs' in the last period. However, I just followed a question in the aps.net forums where I provided an answer on how to update multiple records at once using stored procedure that accepts parameter as comma delimited string. The issue was to create update commands as much as we have number of delimited strings in the parameter.

Here is the stored procedure I wrote for few minutes:

CREATE PROCEDURE UpdateVouchers(@Voucher nvarchar(max))
as
begin
	declare @nextString nvarchar(100)
	declare @p int --position
	declare @np int -- next postion	
	declare @d nvarchar(40) -- delimiter
	declare @TableToUpdate as nvarchar(50)
	set @TableToUpdate = 'dbo.TableName' -- add your table to update
	declare @ColumnToUpdate as nvarchar(50)
	set @ColumnToUpdate = 'ColumnToUpdate' -- add your column to update		
	
	declare @UpdateQuery as nvarchar(max)
	
	set @d = ','
	set @Voucher = @Voucher + @d
	set @p = charindex(@d,@Voucher)
	
	while (@p <> 0)
	begin
		set @nextString = substring(@Voucher,1,@p - 1)
		--select @nextString -- Show Results
		
		set @UpdateQuery =	'UPDATE '+ @TableToUpdate + 
							' SET '+ @ColumnToUpdate + '='+ ''''+ cast(newid() as nvarchar(100)) +''''+
							' WHERE '+ @ColumnToUpdate +'='+ ''''+rtrim(ltrim(@nextString))+''''
		
		print (@UpdateQuery) -- change with exec(@UpdateQuery) to execute it instead of print
		
		set @Voucher = substring(@Voucher,@p+1,len(@Voucher))
		set @p = charindex(@d,@Voucher)
	end
end

To test the procedure, you can do this:

exec UpdateVouchers 'ValidVoucher1, ValidVoucher2'

Or you can simply create method in your C#/VB.NET code and call the procedure with delimited string with N given number of vouchers/strings, eg. "98sa908sa9asha8,s98a87usa98asysaas, 1212091280sa9sa0usa".

Moreover, the WHERE condition should be updated as its needed in your case. In my case was to find the same voucher and update it with the new value, something like UPDATE @tableName SET VoucherCode=NEWID() where VoucherCode=@OldVoucherCode

The printed queries in my case:

UPDATE dbo.TableName SET ColumnToUpdate='0AA8E1C9-6AE0-4BA9-A017-F06DEA96517E' WHERE ColumnToUpdate='Voucher1'
UPDATE dbo.TableName SET ColumnToUpdate='A6283037-1629-4A61-A427-90C7EDFF9F1F' WHERE ColumnToUpdate='Voucher2'

Hope this will help someone out there ;)!

Regards,
Hajan