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

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.

ASKER

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!!.

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.

ASKER

Any Suggestions on how to accomplish or to speak in theories would be greatly appreciated.

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...

ASKER

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

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

might need to check the value of ID in that loop.... if direction is OK, will fix so it doesn't loop for ever...

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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."

ASKER

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.

>>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.

ASKER

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.