AUTO GENERATE SERIAL NUMBER
USE [DatabaseName]
GO
/******
Object: StoredProcedure
[dbo].[SP_GENERATE_SLNO] Script Date:
02/20/2013 17:27:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--
=============================================
-- Author: <DEEP>
-- Create date:
<FEB 02 2013>
-- Description: <Genrate Serial No >
--
=============================================
Create PROCEDURE [dbo].[TABLE_GENERATE_SLNO](
-- Add the parameters for the stored
procedure here
@appName as varchar(10))
---exec [GUESTHOUSE_GENERATE_SLNO]
'GH'
AS
BEGIN
SET NOCOUNT
ON;
DECLARE @SLGEN VARCHAR(50);
DECLARE @SLRESLT VARCHAR(50);
DECLARE @DATEFRMT VARCHAR(50);
SET @DATEFRMT=(SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 4) ,'.','')AS [DD.MM.YY])
INSERT INTO GENERATE_SLNO (TEMP_INS_CHAR) VALUES(1)
SET @SLGEN=@@IDENTITY;
DELETE FROM GENERATE_SLNO WHERE
TEMP_AUTO=@@IDENTITY
SET @SLGEN=(SELECT REPLACE(STR(@SLGEN, 5), SPACE(1), '0'))
IF @@IDENTITY>=99999
BEGIN
TRUNCATE TABLE
END
SET @SLRESLT=@appName+'-'+@DATEFRMT+'-'+cast(@SLGEN as varchar(50))
END
SELECT @SLRESLT
