Solved

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

Posted on 2008-10-23
14
790 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 4
14 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22791832
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
ID: 22791965
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
ID: 22792017
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 1

Author Comment

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

Expert Comment

by:Mark Wills
ID: 22792855
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
ID: 22793438
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
ID: 22794075
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22794178
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
ID: 22797265
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
ID: 22800115
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
ID: 31509483
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
ID: 22801275
>>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
ID: 22805221
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
ID: 22805882
I am afraid, I have no clue what you are trying to say.

Good luck.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

739 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