Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cursors and variables Expand / Collapse
Author
Message
Posted Thursday, May 29, 2008 9:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 20, 2011 10:48 AM
Points: 1, Visits: 12
Hi,
Im trying to use a variable within a cursor but I actually populate the variable after the cursor declaration and it doesn't appear to use the value. I created code for a sample as the code this is not working in is rather long and would be confusing if I posted it

My question is ...
Is this NORMAL behavior ?

For example

declare @tab_name varchar(200), @res_name varchar(200)
--set @tab_name = 'sysowners' -- uncomment this line and the cursor will return a value
--
declare sample cursor for
select name from sysobjects
where lower(name) = lower(@tab_name)
--
set @tab_name = 'sysowners'
open sample
fetch sample into @res_name
while @@fetch_status = 0
begin
print @res_name
fetch sample into @res_name
end
close sample
deallocate sample
Post #508460
Posted Thursday, May 29, 2008 9:13 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,550, Visits: 2,232
Hi,

Not sure if this is normal.
I have never used a cursor in my life (very happy about that)
Do you have to use a cursor for this problem?

Thanks
Chris


----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #508465
Posted Thursday, May 29, 2008 9:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:22 PM
Points: 7,074, Visits: 15,318
I think you're misunderstanding what is going on. The DECLARE CURSOR does use the variable as it's defined at the time when the DECLARE is issued. Meaning - in programmer terminology - the DECLARE CURSOR sets up and initializes the cursor in one statement. There's no deferred resolution going on. The only thing that hasn't happened yet is that the cursor hasn't been materialized (which occurs with the OPEN statement).

In this case you get nothing, since at the time of the declare - the variable is NULL.

This is normal behavior and to be expected.


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #508476
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse