SQL SERVER – Simple Example of Cursors


create proc sp_GetClassCursors
as
begin
Declare @cID varchar(10)
Declare @cName nvarchar(50)
–Cursors
Declare @cur_GetClass cursor

Set @cur_GetClass = cursor for
Select cID,Name from Class

Open @cur_GetClass
fetch next
from @cur_GetClass into @cID, @cName
while @@fetch_status = 0
begin
print @cID+’-‘+@cName
fetch next
from @cur_GetClass into @cID, @cName
end
close @cur_GetClass
deallocate     @cur_GetClass
end
Go
–Execute
exec sp_GetClassCursors

 

 

create proc sp_RunBackup
as
begin
DECLARE @name VARCHAR(50) — database name
DECLARE @path VARCHAR(256) — path for backup files
DECLARE @fileName VARCHAR(256) — filename for backup
DECLARE @fileDate VARCHAR(20) — used for file name

SET @path = ‘C:\doannv\Backup\’

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE cur_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’,’C0312CB’,’J0611NC’)

OPEN cur_cursor
FETCH NEXT FROM cur_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM cur_cursor INTO @name
END

CLOSE cur_cursor
DEALLOCATE cur_cursor
end
Go

Gửi phản hồi

Please log in using one of these methods to post your comment:

WordPress.com Logo

Bạn đang bình luận bằng tài khoản WordPress.com Log Out / Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Log Out / Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Log Out / Thay đổi )

Google+ photo

Bạn đang bình luận bằng tài khoản Google+ Log Out / Thay đổi )

Connecting to %s