Need condition to check if @CUR_TEMP2 is nothing

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 2012 Forums

Transact-SQL (2012)

Need condition to check if @CUR_TEMP2 is nothing

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2015-01-09 : 12:44:27

I am using below cursor, i would like to use a condition check to when curtemp2 has no rows or null or nothing:Can you please advise how to put the condition.
Thanks a lot for the helpful info.


BEGIN
  SET @CUR_TEMP2 = CURSOR SCROLL FOR SELECT TOP 1 A.RMID FROM TAB_RM_log AS A 
	JOIN TAB_RM AS C ON (A.RMID = C.RMID) 
	WHERE A.Updatedby = @UserName 
	    AND A.Deleted = '0' AND C.Deleted = '0' ORDER BY A.Updated desc

If @CUR_TEMP2 has zero rows or nothing then use below query

SELECT max(RMID) FROM TAB_RM_log  
	WHERE Deleted = '0' 
END

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-09 : 13:51:00

First of all, why do you want to use a cursor? What will happen in the body of the loop? Perhaps you don't need a cursor at all.

Second your code is not valid TSQL. Read up on how to define and use cursors and get the syntax right.

http://msdn.microsoft.com/en-CA/library/ms180169.aspx

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2015-01-09 : 14:11:13

I only provided a single block of the sp, this is an existing sp, i don't want to make changes, but later will. would like to add one condition to check if cur_temp2 has any rows within it or not.

Thanks.


DECLARE @CUR_TEMP CURSOR
DECLARE @CUR_TEMP2 CURSOR

DECLARE @Result bit
DECLARE @Message bit

SET @RMRecordReturned= '-1'

--set RM record ID so that we can check whether there is any RM record to default to
SET @RMID = '-1'
--CUR_TEMP will hold all the RM records modified by this user that are not marked deleted and for which user still has access
Set @AgencyID = (select enterpriselevelaccess from TAB_ccsNetUsers where UserName = @UserName)

IF @AgencyID = 1
	BEGIN
		SET @CUR_TEMP = CURSOR SCROLL FOR SELECT A.RMID FROM TAB_CCSNETRM_log  AS A JOIN TAB_CCSNETRM AS C 
		ON (A.RMID = C.RMID) WHERE A.Updatedby = @UserName and A.Deleted = '0' AND C.Deleted = '0' ORDER BY A.Updated
	END
ELSE
	BEGIN
			SET @CUR_TEMP = CURSOR SCROLL FOR SELECT A.RMID FROM TAB_ccsNetRM_log  AS A JOIN TAB_ccsNetUserAccess AS B 
		ON (A.UpdatedBy = B.UserName AND A.ProgID = B.ProgID AND A.ProjID = B.ProjID AND A.ContractID = B.ContractID)
		JOIN TAB_ccsNetRM AS C ON (A.RMID = C.RMID) 
		WHERE B.UserName = @UserName 
			  AND A.Deleted = '0' AND B.Deleted = '0' AND C.Deleted = '0' AND B.ccsNetModule = 'RM' ORDER BY A.Updated
	END

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-09 : 15:07:56

I have to guess what your intentions are since you have not posted enough code. I particular, I do not see a WHILE loop or a FETCH statement that uses either cursor. I guess that @cur_temp2 is set to a query somewhere. You want to know if there will be any rows retrieved by the query used by cur_temp2, right? Then, suppose you have:

set @cur_temp2 = cursor scroll for select ...

to see if there are any rows, write

if exists (select ...)
-- do something if rows exists
else
-- do something else if no rows
end

Don't reference or use the cursor in the test for rows, it will only slow things down.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources