Solved

procedure not passing existing category

Posted on 2012-03-29
5
372 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 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
rolling count by date, hour query 7 27
Search Text in Views 2 24
database audit for object access 6 36
Delete duplicates from SQL Server table 2 18
A procedure for exporting installed hotfix details of remote computers using powershell
Auditing domain password hashes is a commonly overlooked but critical requirement to ensuring secure passwords practices are followed. Methods exist to extract hashes directly for a live domain however this article describes a process to extract u…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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

738 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