How do I get my cursor value to a variable....

I am trying to assign the cursor value to a variable so I can do an insert using it. The problem I have as soon as I try to do fetch into @variable name i break the loop and it gives me the last one in my list.  Below is the code:

Any help on this would be greatly appreciated.
ALTER PROCEDURE [dbo].[sp_update_daily_totals]
	
	@Date datetime
	
AS
BEGIN
	DECLARE @storeid nvarchar(MAX), @cashierid nvarchar(max)
	SET NOCOUNT ON;
 
    DECLARE TBLC_StoreInfo CURSOR
	FOR ( SELECT storeid FROM [CustomerMain].[dbo].[StoreInfo] WHERE active = 1 AND cidx = 1)
	
	OPEN TBLC_StoreInfo
	FETCH NEXT FROM TBLC_StoreInfo into @storeid
	WHILE @@FETCH_STATUS = 0
	
	BEGIN
	Select @storeid
			declare @Var nvarchar(4000)
			set @Var = 'SELECT Distinct(cashier) from [dbo].[' + @storeid + '] where cashier !='''''
			declare @get_data3  CURSOR
			
			set @Var = 'set @incursor = CURSOR FOR '+@Var+' OPEN @incursor'
			
			execute sp_executesql @Var,N'@incursor CURSOR OUT',@incursor=@get_data3 OUT
					
						
			WHILE @@FETCH_STATUS = 0
			
-- if i do it like this it breaks and only gives me the last @cashierid from the selection
 
			fetch next from @get_data3 into @cashierid
 
--if i do it like this it will output all of the @cashierid's to the output window when testing the stored proc..
 
			fetch next from @get_data3 
 
 
			select @cashierid
			close @get_data3
			deallocate @get_data3
	
	
	FETCH NEXT FROM TBLC_StoreInfo into @storeid
			
	END
			CLOSE TBLC_StoreInfo
			DEALLOCATE TBLC_StoreInfo
			
			
			
END

Open in new window

LVL 1
chrobiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
Since you are using dynamic sql to declare the cursor, I suspect you will have to define it as global.  But the big question is why are you using a CURSOR?  Let alone Dynamic SQL.
0
chrobiAuthor Commented:
Here is what i have to do.....

1. I have to loop through all of my tables that have the relevant data
2. Once I have the table with the data i have to go through it and get the unique cashier names/id's
3. then i need to run queries based on the cashier's and put that data in to a table to keep running totals on the cashiers


I do not know what tables are going to hold the data, until i call the SP, I do not know what cashiers I will have until i am looping through the data.  

If I am completely wacked out of my mind please let me know, i have read all about the "Cursor" = BAD NEWS!!.

0
Anthony PerkinsCommented:
I see and you have my condoloncies that you have to work with such a lousy design.  I will let better brains work on this one.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

chrobiAuthor Commented:
Any Suggestions on how to accomplish or to speak in theories would be greatly appreciated.
0
Mark WillsTopic AdvisorCommented:
Cursors are not always BAD NEWS - even MS use them in some of their own procedures... It is just SO easy to abuse, or get it wrong, or use when other methods are better...

in your case, looping through storeid which are tables to then process each table is not such a bad use of a cursor at all..

What is not evident is why you have to open @get_data3  cursor. I can see the table name being the store id , and a select for cashiers - but why then a cursor for those ? is it more than a "simple" sql retrieval of data ? Maybe you should be populating a global temp table in that dynamic sql, then assign a tcursor to that on return from the executesql... But would be interested to hear more about step 3 as to why a cursor is required...

0
chrobiAuthor Commented:
The main reason is in the storid table i have an unknown amount of cashiers, so once i find the out the tables i have to figure out the distinct cashiers.  I have redone the code to use this type template..  Does this look like a better solution?

 
    Declare @au_id Varchar(20),
 
                @Count Int,
 
                @LoopCount Int
 
    Select Identity(int,1,1) ID, au_lname, au_fname into #Temp from authors
 
    Select @Count = @@RowCount
 
    Set @LoopCount = 1
 
    While @LoopCount <= @Count
 
    Begin
 
                Select * from #Temp Where ID = @LoopCount
 
                Set @LoopCount=@LoopCount + 1
 
    End
 
    Drop table #Temp

Open in new window

0
Mark WillsTopic AdvisorCommented:
hang on, I recognize those names and that table... What has that got to do with cashiers... And why are they familiar ?

Thinking more like (and haven't checked the syntax, just typed in) :


 
ALTER PROCEDURE [dbo].[sp_update_daily_totals] (@Date datetime)
AS
BEGIN
 
    DECLARE @storeid nvarchar(MAX), 
            @cashierid nvarchar(max),
            @Var nvarchar(4000),
            @id int
            
    SET NOCOUNT ON;
 
    CREATE TABLE ##tmp_store_cashiers (ID int identity, Storeid varchar(200), Cashier varchar(200))
 
    DECLARE TBLC_StoreInfo CURSOR READ_ONLY FOR  SELECT storeid 
                                                 FROM [CustomerMain].[dbo].[StoreInfo] 
                                                 WHERE active = 1 AND cidx = 1
    
    OPEN TBLC_StoreInfo
    FETCH NEXT FROM TBLC_StoreInfo into @storeid
    WHILE @@FETCH_STATUS = 0
    
    BEGIN
        print @storeid
 
        set @Var = 'INSERT ##tmp_store_cashiers (storeid,cashier) SELECT Distinct ''' + @storeid + ''' as storeid, cashier into ##temp from [dbo].[' + @storeid + '] where cashier !='''''
        exec(@Var)
                    
-- now we have a table of non-empty storeid's and cashiers in ##temp
-- shpould be able to use that in joins and such like to avoid a loop if possible...
 
        while isnull(@id,0) < (select top 1 id from ##tmp_store_cashiers where id < isnull(@id,999999) order by id desc)
        begin
 
-- do something with a cashier
             select @id = id, @cashier = cashier from ##tmp_store_cashiers where storeid = @storeid and id < isnull(@id,999999) order by id desc)
            
        end    
    
        FETCH NEXT FROM TBLC_StoreInfo into @storeid
            
    END
    CLOSE TBLC_StoreInfo
    DEALLOCATE TBLC_StoreInfo
            
END

Open in new window

0
Mark WillsTopic AdvisorCommented:
might need to check the value of ID in that loop.... if direction is OK, will fix so it doesn't loop for ever...
0
Mark WillsTopic AdvisorCommented:
apologies chrobi, that previous code was really very ordinary and bug ridden. Have tried it out and fixed accordingly...
ALTER PROCEDURE [dbo].[sp_update_daily_totals] (@Date datetime)
AS
BEGIN
 
    DECLARE @storeid nvarchar(MAX), 
            @cashier nvarchar(max),
            @Var nvarchar(4000),
            @id int
            
    SET NOCOUNT ON;
 
    DECLARE TBLC_StoreInfo CURSOR READ_ONLY FOR  SELECT storeid 
                                                 FROM [CustomerMain].[dbo].[StoreInfo] 
                                                 WHERE active = 1 AND cidx = 1
    
    OPEN TBLC_StoreInfo
    FETCH NEXT FROM TBLC_StoreInfo into @storeid
    WHILE @@FETCH_STATUS = 0
    
    BEGIN
        print @storeid
 
        IF OBJECT_ID('tempdb.dbo.##tmp_store_cashiers', 'U') IS NOT NULL DROP TABLE ##tmp_store_cashiers;
        set @Var = 'SELECT identity(int,1,1) as ID, ''' + @storeid + ''' as storeid, cashier into ##tmp_store_cashiers from [' + @storeid + '] where cashier !='''' group by cashier'
        exec(@Var)
                    
-- now we have a table of non-empty storeid's and cashiers in ##temp
-- should be able to use that in joins and such like to avoid a loop if possible...
        set @id = 0
 
        while @id < (select top 1 id from ##tmp_store_cashiers where storeid = @storeid order by id desc)
        begin
 
-- do something with a cashier
             select top 1 @id = id, @cashier = cashier from ##tmp_store_cashiers where storeid = @storeid and id > isnull(@id,0) order by id asc
 
             print convert(varchar,@id) + ' ' + @cashier + ' ' + @storeid
                         
        end    
    
        FETCH NEXT FROM TBLC_StoreInfo into @storeid
            
    END
    CLOSE TBLC_StoreInfo
    DEALLOCATE TBLC_StoreInfo
            
END

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chrobiAuthor Commented:
The code looks familiar huh?  Interesting  you would say that...by looking at your profile and the location of your email it seems I owe an aussie some points, the code you supplied works like a charm.  Thank you very much for your time and efforts on this, and appreciate you give me help rather than "I will let better brains work on this one."
0
chrobiAuthor Commented:
g'day Again thank you for your time and brain power to help me understand that the cursor is not a "BAD" thing when used in the correct manner.  And I appreciate that you commented the code to help me learn how the process worked, it is very helpful.  
0
Anthony PerkinsCommented:
>>and appreciate you give me help rather than "I will let better brains work on this one."<<
Pray tell, what would you have done if you did not know the answer? Not respond? I am sorry, that is not the way I was brought up in my country.
0
chrobiAuthor Commented:
Mr. Perkins,

In my country, I just come out and tell you that I don't know the answer to your question, and don't bash the predicament that person is in that is posting the question.  I do appreciate people in this world that know their limits, but it seems that some people just don't know their limits.
0
Anthony PerkinsCommented:
I am afraid, I have no clue what you are trying to say.

Good luck.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.