cursor

[fahimeh2010]

سلام بچه ها
اگه امکان داره یک مثال بزنید که توش از یک کرسر استفاده شده و این کرسر پیوند دو تا جدول هستش
ممنونم

[golbafan]

سلام

if exists(Select * from sys.objects where name=N'#test')
drop table #Test
go
Create table #Test(
TableName NVARCHAR(50),
Title NVARCHAR(50),
[Desc] TEXT,
LastUpdate DATETIME,
Removed BIT)
Go
insert into #Test (Title, [Desc], LastUpdate, Removed,TableName)
select Table2.title, Table2.[desc], Table2.LastUpdate, Table2.removed,'Table2'
from Table2, Table1
where Table2.id = Table1.fk_Table2
go
insert into @Tmp_table (TableName, Title, [Desc], LastUpdate, Removed,TableName)
select Table3.title, Table3.[desc], Table3.LastUpdate, Table3.removed,'Table3'
from Table3, Table1
where Table3.id = Table1.fk_Table3
go
go
Select Title, [Desc], LastUpdate, Removed,TableName

[golbafan]

مثال بعدی:

SET NOCOUNT ON
DECLARE @au_id varchar(11), @au_fname varchar(20), @au_lname varchar(40),
@message varchar(80), @title varchar(80)

PRINT "-------- Utah Authors report --------"

DECLARE authors_cursor CURSOR FOR
SELECT au_id, au_fname, au_lname
FROM authors
WHERE state = "UT"
ORDER BY au_id
OPEN authors_cursor
FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT " "
SELECT @message = "----- Books by Author: " +
@au_fname + " " + @au_lname
PRINT @message
DECLARE titles_cursor CURSOR FOR
SELECT t.title
FROM titleauthor ta, titles t
WHERE ta.title_id = t.title_id AND
ta.au_id = @au_id -- Variable value from the outer cursor
OPEN titles_cursor
FETCH NEXT FROM titles_cursor INTO @title
IF @@FETCH_STATUS <> 0
PRINT " <<No Books>>"
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = " " + @title
PRINT @message
FETCH NEXT FROM titles_cursor INTO @title
END
CLOSE titles_cursor
DEALLOCATE titles_cursor
-- Get the next author.
FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname
END
CLOSE authors_cursor
DEALLOCATE authors_cursor
GO