Search content within the blog

Wednesday, April 22, 2009

Cursors in SqlServer - Part II

Create a table by name Ashwin and perform some inserts into it
here is the code to create the table

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Ashwin](
[Name] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

here is the query to insert values into it
insert into ashwin
select 'value1' union
select 'value2' union
select 'value3'

now we create a cursor which runs through each row and displays the name contained in eachrow
Declare @name varchar(32)
declare cursorname cursor read_only for
select name from Ashwin

open cursorname
fetch NEXT from cursorname into @name
while(@@Fetch_status=0)
begin
print @name
fetch next from cursorname into @name
end
close cursorname
deallocate cursorname


Now we declare a cursor which updates the values present in each row

Declare @nameUpdate varchar(32)
Declare cursorupdate cursor for
select name from Ashwin
for update of name

open cursorupdate
fetch next from cursorupdate into @nameUpdate
while (@@Fetch_status=0)
begin
update ashwin set name='new'+@nameUpdate
where current of cursorupdate

fetch next from cursorupdate into @nameUpdate
end

close cursorupdate
deallocate cursorupdate

select * from Ashwin

No comments:

Post a Comment