Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 377
  • Last Modified:

procedure not passing existing category

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
kdeutsch
Asked:
kdeutsch
  • 4
1 Solution
 
kdeutschAuthor Commented:
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
 
rajeevnandanmishraCommented:
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
 
kdeutschAuthor Commented:
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
 
kdeutschAuthor Commented:
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
 
kdeutschAuthor Commented:
thanks for the help
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now