Solved

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

Posted on 2008-10-23
14
783 Views
Last Modified: 2012-08-13
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

0
Comment
Question by:chrobi
  • 6
  • 4
  • 4
14 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 
LVL 1

Author Comment

by:chrobi
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 
LVL 1

Author Comment

by:chrobi
Comment Utility
Any Suggestions on how to accomplish or to speak in theories would be greatly appreciated.
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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
 
LVL 1

Author Comment

by:chrobi
Comment Utility
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
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
Comment Utility
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
 
LVL 1

Author Comment

by:chrobi
Comment Utility
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
 
LVL 1

Author Closing Comment

by:chrobi
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>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
 
LVL 1

Author Comment

by:chrobi
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
I am afraid, I have no clue what you are trying to say.

Good luck.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now