?
Solved

procedure not passing existing category

Posted on 2012-03-29
5
Medium Priority
?
374 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
[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
  • 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 2000 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

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.

Question has a verified solution.

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

Previously, on our Nano Server Deployment series, we've created a new nano server image and deployed it on a physical server in part 2. Now we will go through configuration.
In this post we will be converting StringData saved within a text file into a hash table. This can be further used in a PowerShell script for replacing settings that are dynamic in nature from environment to environment.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

777 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