Geeks With Blogs
Gaurav Taneja Great dreams... never even get out of the box. It takes an uncommon amount of guts to put your dreams on the line, to hold them up and say, "How good or how bad am I?" That's where courage comes in.

CREATE

@intShowVersion

@strRotationName

@strTrainOperator

@strArrDate

@strOut

PROCEDURE [dbo].[GenTrainRandNo] tinyint, varchar(20), varchar(50), datetime, varchar(15) output

AS

 

SET

 

NOCOUNT ON

IF

 

ELSE

 

(@intShowVersion = 1 ) BEGIN SELECT VERSION='$Revision: 1 $' RETURN 0 END BEGIN DECLARE @intError AS INTEGER DECLARE @strYear AS VARCHAR(4) DECLARE @bitUseYear AS BIT DECLARE @strPrefix AS VARCHAR(10) DECLARE @intCurrentVal AS INTEGER DECLARE @strLen AS INTEGER DECLARE @strCurrentVal VARCHAR(15) SET @intError = 0 SELECT @bitUseYear=ISNULL(ACRM_USE_YEAR_B,0),@strPrefix=ISNULL(ACRM_PREFIX_C,'') FROM ROTATION_MASTER with (nolock) WHERE ACRM_ROTATIONNAME_C=@strRotationName IF @bitUseYear=1 Set @strYear = convert(char(4),@strArrDate,12)--This will gets the value of current year followed by the month.

 

ELSE Set @strYear='' BEGIN BEGIN TRAN update ROTATION_MASTER set ACRM_CURRENTVAL_I = case when MONTH(GETDATE())<>MONTH(ACRM_UPDATEDON_DT) then ACRM_LOWERSCALE_I --IF month given in the updatedby is less than current then reset the value to lowercase.

 

else ACRM_CURRENTVAL_I+ACRM_INCREMENT_I -- else increment the value with the increment value given in table.

 

ACRM_UPDATEDON_DT

 

 

End , = GETDATE() where ACRM_ROTATIONNAME_C = @strRotationName SET @intError =@@Error IF @intError<>0 BEGIN ROLLBACK TRAN RETURN END SELECT @intCurrentVal = ACRM_CURRENTVAL_I FROM ROTATION_MASTER with (nolock) WHERE ACRM_ROTATIONNAME_C=@strRotationName

SET

@strLen = len(@intCurrentVal)

IF

@strLen = 1 BEGIN SET @strCurrentVal = '00'+ convert(varchar(15),@intCurrentVal) END

ELSE

 

IF @strLen = 2 BEGIN SET @strCurrentVal = '0'+ convert(varchar(15),@intCurrentVal) END

ELSE

 

END

BEGIN SET @strCurrentVal = convert(varchar(15),@intCurrentVal) END set @strPrefix = @strPrefix+ LEFT(@strTrainOperator,3) Select TOP 1 @strOut=@strPrefix+@strYear+@strCurrentVal from APOLLO_CFS_ROTATION_MASTER where ACRM_ROTATIONNAME_C = @strRotationName COMMIT TRAN END

SET

NOCOUNT OFF

Posted on Thursday, January 15, 2009 8:50 PM | Back to top

Copyright © Gaurav Taneja | Powered by: GeeksWithBlogs.net