Cursor Open - 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

General SQL Server Forums

New to SQL Server Programming

Cursor Open

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-06 : 10:01:10

How do i open cursor for two different select?

USE Master
GO
DECLARE @DW VARCHAR(20)

SET @DW = DATENAME(dw, GETDATE())
DECLARE C CURSOR FOR
If @DW='Saturday'
SELECT [name] FROM sys.Databases WHERE [name] NOT IN ('tempdb')
Else IF @DW='Sunday'
SELECT [name] FROM sys.Databases WHERE [name] LIKE ('DB10%')
Else
SELECT [name] FROM sys.Databases WHERE [name] LIKE ('XYZ%')
OPEN c
FETCH NEXT FROM c INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
Print 'Database : '+ @name + ' processing'
If @DW='Saturday'
BEGIN
--i have to do something here
END
Else IF @DW='Sunday'
BEGIN
--i have to do something here

END

FETCH NEXT FROM c INTO @name
END
CLOSE c
DEALLOCATE c

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-06 : 10:22:34

One solution could be to use your IF/ELSE blocks to populate the appropriate db names into a temp table. Then declare your cursor for whatever db names ended up in your temp table.

Be One with the Optimizer
TG

pootle_flump

1064 Posts

Posted - 2009-03-06 : 10:24:30

quote:
Originally posted by rudba

How do i open cursor for two different select?

That's a much better question!

How about:

USE Master
GO
DECLARE @DW VARCHAR(20)

SET @DW = DATENAME(dw, GETDATE())
DECLARE C CURSOR FOR
SELECT [name] FROM sys.Databases 
WHERE 
([name] NOT IN ('tempdb') AND @DW='Saturday')
OR
([name] LIKE ('DB10%') AND @DW='Sunday')
OR
([name] LIKE ('XYZ%') AND @DW NOT IN('Sunday', 'Saturday'))

OPEN c
FETCH NEXT FROM c INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
Print 'Database : '+ @name + ' processing'
--i have to do something here

FETCH NEXT FROM c INTO @name
END
CLOSE c
DEALLOCATE c

EDIT - missed a parethen....a paren.... a par... a bracket.

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-06 : 10:28:39

Thanks pootle_flump,

quote:
Originally posted by pootle_flump

quote:
Originally posted by rudba

How do i open cursor for two different select?
That's a much better question!

How about:
USE Master
GO
DECLARE @DW VARCHAR(20)

SET @DW = DATENAME(dw, GETDATE())
DECLARE C CURSOR FOR
SELECT [name] FROM sys.Databases
WHERE
([name] NOT IN ('tempdb') AND @DW='Saturday')
OR
([name] LIKE ('DB10%') AND @DW='Sunday')
OR
([name] LIKE ('XYZ%') AND @DW NOT IN('Sunday', 'Saturday'))

OPEN c
FETCH NEXT FROM c INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
Print 'Database : '+ @name + ' processing'
--i have to do something here

FETCH NEXT FROM c INTO @name
END
CLOSE c
DEALLOCATE c

EDIT - missed a parethen....a paren.... a par... a bracket.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources