Run multiple sql statments with a variable

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 2005 Forums

SSIS and Import/Export (2005)

Run multiple sql statments with a variable

Author

Topic

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 2007-10-30 : 11:20:41

I am not sure how to do this. I need to run 3 sql statements against a table with a variable created in one of them.

Here is the first statement

Select ID from table1 where value = 1

Need to store that value in a variable

Update table1
Set value = 0
Where ID = variable

Update table1
Set value = 1
Where ID = variable + 1

ID is a incremental identity field, so it is numeric. Basically I need to change the value of one record to 0, and make the next records value = one.

Any help is appreciated.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-30 : 12:28:44

Why not do this in a single batch (in fact a stored proc would be best)?
create proc a
as
declare @id int
select @id = ID from table1 where value = 1
update table1 set value = 0 where ID = @id
update table1 set value = 1 where ID = @id+1
go

or you could do it in a single statement

update t1
set t1.ID = case when t1.ID = t2.ID+1 then 1 else 0 end
from Table1 t1
join (select ID from Table1 where value = 1) t2
on t1.ID in (t2.ID, t2.ID + 1)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources