SQL stored procedure cs_00000001 generate a sequential account number

 

If you want to obtain the serial number of cs_00000001, cs_00000002, and cs_00000003
The following stored procedure

Create procedure [DBO]. [sp_room_code]
@ Codeid varchar (11) Output -- // cs_00000001 the length of the sequential account number. Here I use 11 characters
As

Declare @ num int -- // defines

Select @ num = max (cast (substring (room_code, 3,4) as INT) from yourtable
-- // Obtain the maximum value after the room_code sequential account number field. Save the value @ num
-- This statement uses three SQL functions: Max, cast, and substring. For their usage, refer to the help documentation provided by server.
Set @ num = @ num + 1 -- // Add 1

Set @ codeid = 'cs _ '+ right ('000000' + Cast (@ num as varchar (8), 8)
-- // This sentence is the key to generating the sequential account number
-- // Its format is right (the length is the length of the serial number, such as the numerical length of the serial number plus the length of the cast (@ num as serial number), the serial number length)
-- // The Right function is used here.

Go

SQL Server books online function reference

Max
Returns the maximum value of an expression.

Syntax
Max ([All | distinct] expression)

Parameters
All

Performs aggregate function operations on all values. All is the default setting.

Distinct

Each unique value is considered. Distinct makes no sense for Max and uses it only to comply with SQL-92 compatibility.

Expression

Any combination of constants, column names, functions, Arithmetic Operators, bitwise operators, and string operators. Max can be used for numeric, character, and datetime columns, but not bit columns. Aggregate functions and subqueries are not allowed.

Return type
Returns the same type as expression.

Important when cube or rollup is used, differential aggregation is not supported, such as AVG (distinct column_name), count (distinct column_name), max (distinct column_name), min (distinct column_name) and sum (distinct column_name ). If it is used, Microsoft SQL server returns an error message and cancels the query.

Note
Max ignores any null values.

For character columns, Max searches for the maximum value of the sorting sequence.

Example
The following example returns the book with the highest annual sales.

Use pubs
Go
Select max (ytd_sales)
From titles
Go

The following is the result set:

-----------
22246

(1 row (s) affected)

Warning, null value eliminated from aggregate.

========================================================== ====================

Substring
Returns a part of a character, binary, text, or image expression. For more information about valid Microsoft SQL server data types that can be used with this function, see data types.

Syntax
Substring (expression, start, length)

Parameters
Expression

Is a string, binary string, text, image, column, or expression that contains a column. Do not use expressions that contain aggregate functions.

Start

It is an integer that refers to the starting position of the stator string.

Length

It is an integer that refers to the length of the substring (the number of characters to return or the number of bytes ).

This indicates that because the start and length values of substring are specified for text data, DBCS data (such as Japanese characters) may split characters at the start or end of the result. This behavior is consistent with the way that readtext processes DBCS. However, due to occasional strange results, we recommend that you use ntext instead of text for DBCS characters.

The following example shows how to return only a part of the string. This query returns the last name in the authors table in one column, and the first letter in the authors table in the other column.

Use pubs
Select au_lname, substring (au_fname, 1, 1)
From authors
Order by au_lname

The following is the result set:

Au_lname
-----------------------------------------
Bennet
Blotchet-Hils R
Carson C
Defrance m
Del Castillo I
...
Yokomoto

(23 row (s) affected)

The following example shows how to display the second, third, and fourth characters in the String constant abcdef.

Select x = substring ('abcdef', 2, 3)

The following is the result set:

X
----------
BCD

(1 row (s) affected)

========================================================== ====================

Right
Returns the number of integer_expression characters specified from the right.

Syntax
Right (character_expression, integer_expression)

Parameters
Character_expression

An expression composed of character data. Character_expression can be a constant, variable, or a column of character or binary data.

Integer_expression

Is the starting position, expressed in a positive integer. If integer_expression is negative, an error is returned.

Return type
Varchar

Character_expression must be a data type that is implicitly converted to varchar. Otherwise, use cast to explicitly convert character_expression.

Note
The compatibility level may affect the return value. For more information, see sp_dbcmptlevel.

Example
In the following example, the rightmost five characters in each author's name are returned.

Use pubs
Go
Select right (au_fname, 5)
From authors
Order by au_fname
Go

The following is the result set:

------------------
Raham
Akiko
Lbert
Ann
Anne
Burt
Rene
Heryl
Dean
Dirk
Ather
Innes
Hnson
Livia
Jorie
Ander
Chael
Ichel
Gstar
Inald
Heryl
Earns
Ylvia
(23 row (s) affected)