This is my table where i want my PNRNo to be generated as 'PNRRES001' for the first entry, and consecutive entries with 'PNRRES002','PNRRES002' so on.
So while creating table only i called that column to function which will generate the PNR no, User just has to enter the CustomerNo from the front end, and data wit PNR & Customer No will updated to the PNRDetails table.
CREATE TABLE PNRDetails(PNRNo AS (DBO.FuncIncPNR()) ,customerNo INT
ALTER FUNCTION dbo.FuncIncPNR()
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @RR VARCHAR(20) SET @RR='PNRRESA001'
IF((SELECT COUNT(*)FROM PNRDetails)=0)
BEGIN
RETURN @RR
END
ELSE
BEGIN
DECLARE @pnr VARCHAR(20),@S1 VARCHAR(20),@S2 INT
DECLARE PNRCursor CURSOR Static
FOR SELECT PNRNo FROM PNRDetails
OPEN PNRCursor
FETCH LAST FROM PNRNo INTO @pnr
SET @S1=SUBSTRING(@pnr,1,7)
SET @S2=RIGHT(@PNR,3)
SET @S2=@S2+1;
SET @pnr=@S1+@S2;
END
RETURN @pnr
END
INSERT INTO PNRDetails VALUES(5)
SELECT * FROM PNRDetails
U can run this.And pls do help if u find anything that could help me. any help will be appreciated...
Waiting for your kind response...