· 7 years ago · Nov 28, 2018, 10:22 AM
1Create function Fun_Auto_Insert()
2returns nchar(3)
3as
4BEGIN
5 DECLARE @Seat_No NCHAR(3) = 'A1'
6 declare @Name CHAR(30)
7 declare @Contact_No INT
8 declare @Row_No INT
9 declare @Row_ID CHAR(1)
10 declare @ASCII_No INT
11 declare @CHAR_ID CHAR
12
13IF (SELECT count(*) FROM MOVIE_TICKETS) = 0
14 BEGIN
15 RETURN @Seat_No
16 END
17ELSE
18 SELECT @Seat_No= MAX(SEAT_NO) FROM MOVIE_TICKETS
19
20 SELECT @Row_No=SUBSTRING( @Seat_No,2,LEN(@Seat_No)-1) FROM MOVIE_TICKETS
21
22IF @Row_No=12
23 BEGIN
24 SET @Row_ID=SUBSTRING(@Seat_No,1,1)
25 --convert to ASCII NO
26 SET @ASCII_No=ASCII(@Row_ID)
27 SET @ASCII_No=@ASCII_No+1
28 --convert to CHAR
29 SET @CHAR_ID=CHAR(@ASCII_No)
30 SET @Row_No=1
31 SET @Seat_No=CONCAT(@CHAR_ID,@Row_No)
32 RETURN @Seat_No
33 END
34ELSE
35 SET @Row_No=@Row_No+1
36
37SET @Row_ID=SUBSTRING(@Seat_No,1,1)
38SET @Seat_No=CONCAT(@Row_ID,@Row_No)
39
40RETURN @Seat_No
41
42END
43
44GO
45
46SEAT_NO CONTACT_NO NAME
47A1 8444 BHARATH
48A2 8444 BHARATH
49A3 8444 BHARATH
50A4 8444 BHARATH
51A5 8444 BHARATH
52A6 8444 BHARATH
53A7 8444 BHARATH
54A8 8444 BHARATH
55A9 8444 BHARATH
56A10 8444 BHARATH
57A10 8444 BHARATH
58A10 8444 BHARATH
59A10 8444 BHARATH
60A10 8444 BHARATH
61
62Create function Fun_Auto_Insert()
63returns nchar(3)
64as
65BEGIN
66 DECLARE @Seat_No NCHAR(3);
67
68;WITH CTE AS
69(SELECT Alphas+vals AS SEAT_NO
70 ,Alphas
71 ,vals
72FROM (
73 select CHAR(number) Alphas
74 from master..spt_values
75 WHERE number >= 65 and number <= 90
76 GROUP BY number) A
77 CROSS APPLY (VALUES ('1'),('2'),('3'),('4')
78 ,('5'),('6'),('7'),('8')
79 ,('9'),('10'),('11'),('12'))C(vals)
80)
81SELECT TOP 1 @Seat_No = SEAT_NO
82FROM CTE C
83WHERE NOT EXISTS (SELECT 1
84 FROM MOVIE_TICKETS
85 WHERE C.SEAT_NO = Seat_No)
86ORDER BY Alphas ASC , CAST(vals AS INT) ASC
87
88 RETURN @Seat_No;
89END
90GO
91
92CREATE TABLE MOVIE_TICKETS(Seat_No VARCHAR(3))
93GO
94INSERT INTO MOVIE_TICKETS VALUES ('A2'), ('A10'), ('B2')
95GO
96
97SELECT dbo.Fun_Auto_Insert()
98GO
99
100A1