Solved

procedure not passing existing category

Posted on 2012-03-29
5
369 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
ID: 37781597
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
ID: 37781640
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
ID: 37781646
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
ID: 37781679
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
ID: 37781681
thanks for the help
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Synchronize a new Active Directory domain with an existing Office 365 tenant
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

863 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

25 Experts available now in Live!

Get 1:1 Help Now