cursor is null or not - 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

SQL Server 2000 Forums

Transact-SQL (2000)

cursor is null or not

Author

Topic

raju2647
Starting Member

22 Posts

Posted - 2005-10-31 : 06:45:32

Hi all,

Need to know how to check if the cursor opened contains rows or not.

Thanks in advance

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-10-31 : 06:49:42

use isnull function

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-31 : 06:52:03

Why do you need cursor?
Look for @@CURSOR_ROWS in Books On Line, SQL Server help file

Madhivanan

Failing to plan is Planning to fail

raju2647
Starting Member

22 Posts

Posted - 2005-10-31 : 06:52:43

i want to check if the cursor is empty and if empty iwant to execute another sql stmt.
can u tell me how to cehck if a cursor is empty by using if stmt

raju2647
Starting Member

22 Posts

Posted - 2005-10-31 : 06:57:50

my function is as follows

create function fn_get_versioned_criterionsubgroup(@pk int,@date datetime)
returns
@table table(

[PK_CRITERION_GROUP_ID] [int] NOT NULL ,
[PK_CRI_GRP_START_DATE] [datetime] NOT NULL ,
[CRI_GRP_END_DATE] [datetime] NULL ,
[CRI_GRP_NAME] [varchar] (1048) NULL ,
[CRI_GRP_TITLE] [varchar] (1048) NULL ,
[CRI_GRP_ORDER_INDEX] [int] NULL ,
[CRI_GRP_DESC] [text] NULL ,
[PK_CRITERION_SUB_GROUP_ID] [int] NOT NULL ,
[PK_CRI_SUB_GRP_START_DATE] [datetime] NOT NULL ,
[CRI_SUB_GRP_END_DATE] [datetime] NULL ,
[FK_CRITERION_GROUP_ID] [int] NULL ,
[FK_CRI_GRP_START_DATE] [datetime] NULL ,
[CRI_SUB_GRP_NAME] [varchar] (1048) NULL ,
[CRI_SUB_GRP_TITLE] [varchar] (1048) NULL ,
[CRI_SUB_GRP_ORDER_INDEX] [int] NULL ,
[CRI_SUB_GRP_DESC] [text] NULL,
[PK_CRITERION_ID] [int] NOT NULL ,
[PK_CRI_START_DATE] [datetime] NOT NULL ,
[CRI_END_DATE] [datetime] NULL ,
[FK_CRITERION_SUB_GROUP_ID] [int] NULL ,
[FK_CRI_SUB_GRP_START_DATE] [datetime] NULL ,
[FK_ENTITY_TYPE_ID] [int] NULL ,
[CRI_NAME] [varchar] (2048) NULL ,
[CRI_MAX_SCORE] [int] NULL ,
[CRI_ORDER_INDEX] [int] NULL ,
[CRI_DESC] [text] NULL ,
[CRI_TIP] [text] NULL )
as
Begin
declare @temptable table(
[PK_CRITERION_GROUP_ID] [int] NOT NULL ,
[PK_CRI_GRP_START_DATE] [datetime] NOT NULL ,
[CRI_GRP_END_DATE] [datetime] NULL ,
[CRI_GRP_NAME] [varchar] (1048) NULL ,
[CRI_GRP_TITLE] [varchar] (1048) NULL ,
[CRI_GRP_ORDER_INDEX] [int] NULL ,
[CRI_GRP_DESC] [text] NULL ,
[PK_CRITERION_SUB_GROUP_ID] [int] NOT NULL ,
[PK_CRI_SUB_GRP_START_DATE] [datetime] NOT NULL ,
[CRI_SUB_GRP_END_DATE] [datetime] NULL ,
[FK_CRITERION_GROUP_ID] [int] NULL ,
[FK_CRI_GRP_START_DATE] [datetime] NULL ,
[CRI_SUB_GRP_NAME] [varchar] (1048) NULL ,
[CRI_SUB_GRP_TITLE] [varchar] (1048) NULL ,
[CRI_SUB_GRP_ORDER_INDEX] [int] NULL ,
[CRI_SUB_GRP_DESC] [text] NULL,
[PK_CRITERION_ID] [int] NOT NULL ,
[PK_CRI_START_DATE] [datetime] NOT NULL ,
[CRI_END_DATE] [datetime] NULL ,
[FK_CRITERION_SUB_GROUP_ID] [int] NULL ,
[FK_CRI_SUB_GRP_START_DATE] [datetime] NULL ,
[FK_ENTITY_TYPE_ID] [int] NULL ,
[CRI_NAME] [varchar] (2048) NULL ,
[CRI_MAX_SCORE] [int] NULL ,
[CRI_ORDER_INDEX] [int] NULL ,
[CRI_DESC] [text] NULL ,
[CRI_TIP] [text] NULL )
declare @critpkid int
declare @crit_cursor cursor
set @crit_cursor = CURSOR FAST_FORWARD
For
select distinct PK_CRITERION_ID from CRITERION_SUB_GROUP,CRITERION where
PK_CRITERION_SUB_GROUP_ID=FK_CRITERION_SUB_GROUP_ID and PK_CRITERION_SUB_GROUP_ID=@pk

open @crit_cursor
fetch next from @crit_cursor
into @critpkid
if( Cursor_STATUS('@crit_cursor') = 0)
begin
insert @temptable
select top 1 * from CRITERION_GROUP left outer join CRITERION_SUB_GROUP
on PK_CRITERION_GROUP_ID=FK_CRITERION_GROUP_ID left outer join
CRITERION on PK_CRITERION_SUB_GROUP_ID=FK_CRITERION_SUB_GROUP_ID where PK_CRITERION_SUB_GROUP_ID=@pk
and PK_CRI_SUB_GRP_START_DATE <= cast(@date as datetime) and
PK_CRI_GRP_START_DATE <= cast(@date as datetime)
order by PK_CRI_GRP_START_DATE desc ,PK_CRI_SUB_GRP_START_DATE desc

end
else
begin

while @@FETCH_STATUS = 0
begin
insert @temptable
select top 1 * from CRITERION_GROUP left outer join CRITERION_SUB_GROUP
on PK_CRITERION_GROUP_ID=FK_CRITERION_GROUP_ID left outer join
CRITERION on PK_CRITERION_SUB_GROUP_ID=FK_CRITERION_SUB_GROUP_ID where PK_CRITERION_SUB_GROUP_ID=@pk
and PK_CRITERION_ID = @critpkid and PK_CRI_SUB_GRP_START_DATE <= cast(@date as datetime) and
PK_CRI_START_DATE <= cast(@date as datetime) and
PK_CRI_GRP_START_DATE <= cast(@date as datetime)
order by PK_CRI_GRP_START_DATE desc ,PK_CRI_SUB_GRP_START_DATE desc,
PK_CRI_START_DATE desc

fetch next from @crit_cursor
into @critpkid
end
end
close @crit_cursor
deallocate @crit_cursor
insert @table
select * from @temptable
RETURN
End

GO

the condition in bold is where i would check the cursor

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-10-31 : 06:59:09

Well Madihivanan is right, cursors do have a problem. And its best practice not to use them.

however you could also use coalesce or isnull function, but do similar tests.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources