multiple updates in 1 query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

multiple updates in 1 query

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2006-11-04 : 08:44:06

Hi, I want to make a sp where I update multiple records at once.
I've gotten this far, but I know it is not going to work this way.
I need some kind of loop or something. Can anyone help?

ALTER PROCEDURE [dbo].[UpdateSomeTable]
@NCID int,
@SomeCSVStringWithIDs nvarchar(200)
AS

UPDATE SomeTable SET
NCID = @NCID
WHERE
PrimaryID = dbo.csvToInt(@SomeCSVStringWithIDs )

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-04 : 08:58:56

csvToInt is a table function ?


UPDATE u
SET    NCID = @NCID
FROM       SomeTable u 
INNER JOIN dbo.csvToInt(@SomeCSVStringWithIDs) c
ON         u.PrimaryID = c.intcol

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2006-11-04 : 09:04:31

dbo.csvToInt is a function:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/*****************************************************/
ALTER Function [dbo].[CsvToInt] ( @Array varchar(1000))
returns @IntTable table
(IntValue int)
AS
begin

declare @separator char(1)
set @separator = ','

declare @separator_position int
declare @array_value varchar(1000)

set @array = @array + ','

while patindex('%,%' , @array) <> 0
begin

select @separator_position = patindex('%,%' , @array)
select @array_value = left(@array, @separator_position - 1)

Insert @IntTable
Values (Cast(@array_value as int))

select @array = stuff(@array, 1, @separator_position, '')
end

return
end

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2006-11-04 : 16:09:10

So where you said c.intcol
Should be: c.IntValue

in my case

Thanks anyway

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-04 : 20:54:14

quote:
Originally posted by trouble2

So where you said c.intcol
Should be: c.IntValue

in my case

Thanks anyway

Yes. Change intcol to IntValue

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources