SQL Server writes automatically-increasing string-type primary keys. SQL Server

SQL Server writes automatically-increasing string-type primary keys. SQL Server

1. Compile the scalar value function ICONVERT2STRING

CREATE FUNCTION ICONVERT2STRING(@N INT,@F INT) RETURNS VARCHAR(10) ASBEGINDECLARE @M INTDECLARE @L0 INTDECLARE @CH VARCHAR(10)DECLARE @ST VARCHAR(20)SELECT @CH=''SELECT @ST=CONVERT(VARCHAR(10),@N)SELECT @ST=RTRIM(@ST)SELECT @L0=LEN(@ST)SELECT @M=1WHILE (@M<=@F-@L0)BEGINSELECT @CH=@CH+'0'SELECT @M=@M+1ENDSELECT @ST=@CH+@STRETURN @STEND

Note: @ F is the length of the variable part after the string.

2. Compile the scalar value function GetUnitSNo that returns the primary key string.

CREATE function GetUnitSNo() returns char(8) as beginDeclare @mystr char(8)Declare @str0 char(2)Declare @count intDeclare @MaxEnd intDeclare @st1 char(10)select @str0='US'select @count=count(*) from Unit_Sif @count=0select @MaxEnd=0elseselect @MaxEnd=max(substring(U#,3,len(U#)-1)*1) from Unit_S SELECT @st1=DBO.ICONVERT2STRING(@MaxEnd+1,6)select @mystr=@str0+ LTRIM(RTRIM(@st1))return @mystrend

Note: The Stored Procedure of the returned primary key string has not been implemented ....
3. effect display

SQL query statement: SELECT DBO. GetUnitSNo ()

Query Result: US000001 (the records in the Unit_S table are empty)

4. C # frontend call

String USNO = DB. ExecuteSQLScalarTOstring ("select dbo. GETUNITSNO ()");

Auto-numbered SQL server 2005 Enterprise Manager

Auto-increment cannot start with 0001. The auto-increment type can only be numerical, so it should start from 1.
Custom addition:




How to auto-increment primary keys of the primary SQL SERVER

Auto-increment columns are only applicable to numeric fields. If character fields are used, you must manually trigger them to implement auto-increment.