Solved

procedure not passing existing category

Posted on 2012-03-29
5
368 Views
Last Modified: 2012-03-29
I have a procedure that is creating a special number for each category that sits in the DB.  So for each new category it should create a new ID, but what is happening is its putting a new ID on every record and the same one at that.  the the db it pick from just choses the intrecId and the intcategoryId, then for the 1st one it should create a new number and the rest should only be created if the category changes, what I have should work but it puts the same number on every db entry.

ALTER Proc [dbo].[sp_USPFOTurnIn] (@LocId as int)
AS
BEGIN

      Declare @CatId int, @RecId int, @CatExist int, @ExistJulian varchar(6), @ExistIncriment float, @TJulian varchar(6), @NIncriment float, @NJulian varchar(17)
      
      set @CatExist = 0
      set @TJulian = Cast(Right(Year(getDate()),2) as varchar) + cast(datepart(dy, getdate())as varchar)
      
      Declare USPFO cursor for  Select      tt.intRecId, c.intCategoryId
                                                From      Drat_tempturnin tt INNER JOIN
                                                            Drat_Received dr on dr.intRecId = tt.intRecId INNER JOIN
                                                            Drat_Model dm on dm.intModelId = dr.intModelId INNER JOIN
                                                            Drat_make m on m.intMakeId = dm.intMakeId INNER JOIN
                                                            Drat_Manufacturer mm on mm.intManufacturerId = m.intManufacturerId INNER JOIN
                                                            Drat_Category c on c.intCategoryId = mm.intCategoryId where tt.intLocation = 2 Order by c.intCategoryId;
      
      OPEN USPFO
      
      FETCH NEXT FROM USPFO INTO @RecId, @CatId
      
      while @@FETCH_STATUS = 0

      BEGIN
            
            If @CatExist <> @CatId      
            print @CatExist
            print @CatId
                  --Get the existing Julian information
                  Select @ExistJulian = strjulianDate, @ExistIncriment = intIncrimentNum from Drat_DDN;
                  --Do the updates
                  BEGIN
                        if @ExistJulian = @TJulian
                              set @NIncriment = @ExistIncriment + 1;
                        else
                              set @NIncriment = 2000;
                              
                        Set @NJulian = '90HP7' +  '-' + @TJulian + '-' +Cast(@NIncriment as varchar);
                        
                        Update Drat_TempTurnIn set strDDN = @NJulian where intRecId = @RecId
                  Set @CatExist = @CatId
                  END      
      
      FETCH NEXT FROM USPFO INTO @RecId, @CatId
      
      END

      Close USPFO
      Deallocate USPFO
END
0
Comment
Question by:kdeutsch
  • 4
5 Comments
 

Author Comment

by:kdeutsch
Comment Utility
ok found out one error I was not updating the incriment to the tables so that it would incriment, so now it does incriment .

Update Drat_DDN set strJulianDate = @TJulian, intIncrimentNum = @NIncriment;
0
 
LVL 9

Accepted Solution

by:
rajeevnandanmishra earned 500 total points
Comment Utility
Hi,
I am not sure about the update statement what you have just given. It could be one error. But I think you are missing the scope of If @CatExist <> @CatId . By default If scopes only to next statement. If we want to put more than one statement in If, then we must put a block of BEGIN ... END.

Try to replace your code with the attached code.

ALTER Proc [dbo].[sp_USPFOTurnIn] (@LocId as int)
AS
BEGIN

      Declare @CatId int, @RecId int, @CatExist int, @ExistJulian varchar(6), 
	@ExistIncriment float, @TJulian varchar(6), @NIncriment float, @NJulian varchar(17)
      
      set @CatExist = 0
      set @TJulian = Cast(Right(Year(getDate()),2) as varchar) + cast(datepart(dy, getdate())as varchar)
      
      Declare USPFO cursor for  
	Select      tt.intRecId, c.intCategoryId
	From      Drat_tempturnin tt INNER JOIN
	    Drat_Received dr on dr.intRecId = tt.intRecId INNER JOIN
	    Drat_Model dm on dm.intModelId = dr.intModelId INNER JOIN
	    Drat_make m on m.intMakeId = dm.intMakeId INNER JOIN
	    Drat_Manufacturer mm on mm.intManufacturerId = m.intManufacturerId INNER JOIN
	    Drat_Category c on c.intCategoryId = mm.intCategoryId where tt.intLocation = 2 Order by c.intCategoryId;
      
      OPEN USPFO
      
      FETCH NEXT FROM USPFO INTO @RecId, @CatId
      
	while @@FETCH_STATUS = 0
	BEGIN
		If @CatExist <> @CatId      
		begin 
			print @CatExist
			print @CatId
			--Get the existing Julian information
			Select @ExistJulian = strjulianDate, @ExistIncriment = intIncrimentNum from Drat_DDN;

			if @ExistJulian = @TJulian
				set @NIncriment = @ExistIncriment + 1;
			else
				set @NIncriment = 2000;
		
			Set @NJulian = '90HP7' +  '-' + @TJulian + '-' +Cast(@NIncriment as varchar);
		end
		--Do the updates
		Update Drat_TempTurnIn set strDDN = @NJulian where intRecId = @RecId
		Set @CatExist = @CatId
		
		FETCH NEXT FROM USPFO INTO @RecId, @CatId
	
	END

      Close USPFO
      Deallocate USPFO
END

Open in new window

0
 

Author Comment

by:kdeutsch
Comment Utility
ok, so now I am down to a differnet strDDN is put on every record I put items alike in here and yet the strDDn is still different.

788      90HP7-1289-2021      2263      2  First one  788 & 789 should be same #
789      90HP7-1289-2022      2263      2
803      90HP7-1289-2025      2263      2  First one of new category new NUm
804      90HP7-1289-2023      2263      2  first of new category should 804 & 806 same
806      90HP7-1289-2024      2263      2
0
 

Author Comment

by:kdeutsch
Comment Utility
Hi,

Ok I changed to your code and it works.  This is correct.  I see what I was doing wrong with the Begin and end statements.

788      90HP7-1289-2026      2263      2
789      90HP7-1289-2026      2263      2
803      90HP7-1289-2028      2263      2
804      90HP7-1289-2027      2263      2
806      90HP7-1289-2027      2263      2
0
 

Author Closing Comment

by:kdeutsch
Comment Utility
thanks for the help
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
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.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

743 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

15 Experts available now in Live!

Get 1:1 Help Now