sp to iterate int value to max of 100 and reset

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

sp to iterate int value to max of 100 and reset

Author

Topic

MrBloom
Starting Member

36 Posts

Posted - 2014-01-18 : 14:48:26

hi! I have two columns box and location in a table

I would like to iterate the location column by one when a new row is inserted.
However location column should only reach a max of 100 and then should iterate box column by 1 and the location column revert to 1.

I have come up this this stored procedure which selects the max of location column and iterates this by one and updates it back to the table.
But I realize that the part which updates box column when location reaches a max of 100 will not work as then 100 will always be the max int in the column when it reverts to 0.

Is there a way to adapt this or to do it better.

thanks




  CREATE Procedure [LAB].[UpdateLocationDNA]
@volunteerID INT, 
@StudyNameID INT


As
Begin
    DECLARE @id INT 
SET @id = (SELECT MAX(Location) FROM  [LAB].[tblDNAlocation]) 
IF @id is NULL 
BEGIN 
 SET @id = 0
UPDATE [LAB].[tblDNAlocation]
SET @id = Location = @id + 1 
WHERE Location is null AND  VolunteerID = @volunteerID AND  StudyNameID = @StudyNameID 


END 


ELSE IF @id = 100
BEGIN
DECLARE @Boxid INT 
SET @Boxid = (SELECT MAX(Box) FROM  [LAB].[tblDNAlocation]) 
SET @id = 0
UPDATE [LAB].[tblDNAlocation]
SET @id = Location = @id + 1 , Box = @Boxid + 1
WHERE Location is null  AND  VolunteerID = @volunteerID AND  StudyNameID = @StudyNameID
END


ELSE 


UPDATE [LAB].[tblDNAlocation]
SET @id = Location = @id + 1 
WHERE Location is null  AND  VolunteerID = @volunteerID AND  StudyNameID = @StudyNameID



End

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-01-18 : 23:18:05

[code]
DECLARE @id INT
SET @id = (SELECT MAX(Location) FROM [LAB].[tblDNAlocation])

UPDATE [LAB].[tblDNAlocation]
SET Location = (isnull(@id, 0) % 100) + 1
WHERE Location is null
AND VolunteerID = @volunteerID
AND StudyNameID = @StudyNameID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-19 : 02:40:12

I cant understand the reason for doing this. Isnt Location field the unique values used to identify a location? whats purpose of duplicating it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MrBloom
Starting Member

36 Posts

Posted - 2014-01-19 : 05:23:28

Thanks, this is what I came up with



USE [LabBR1]
GO
/****** Object:  StoredProcedure [LAB].[UpdateLocationDNA]    Script Date: 01/19/2014 09:20:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

  ALTER Procedure [LAB].[UpdateLocationDNA]
@volunteerID INT, 
@StudyNameID INT


As
Begin

   DECLARE @Boxid INT 
SET @Boxid = (SELECT MAX(Box) FROM  [LAB].[tblDNAlocation])
DECLARE @id INT 
SET @id = (SELECT MAX(Location) FROM  [LAB].[tblDNAlocation] WHERE Box = @Boxid ) 


IF @id = 100

BEGIN
SET @id = 0
SET @Boxid = @Boxid +1
UPDATE [LAB].[tblDNAlocation]
SET    Location = (isnull(@id, 0) % 100) + 1, Box = @Boxid
WHERE  Location    is null 
AND    VolunteerID = @volunteerID 
AND    StudyNameID = @StudyNameID 
END


ELSE
UPDATE [LAB].[tblDNAlocation]
SET    Location = (isnull(@id, 0) % 100) + 1,  Box = (isnull(@Boxid, 1)% 100) 
WHERE  Location    is null 
AND    VolunteerID = @volunteerID 
AND    StudyNameID = @StudyNameID 

End

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources