SQL Server writes a string-type primary key that grows automatically

1. Write scalar value function iconvert2string

CREATE FUNCTION iconvert2string (@N int,@f INT) RETURNS varchar (ten) Asbegindeclare @M intdeclare @L0 intdeclare @CH VARCHAR (10) DECLARE @ST VARCHAR () Select @CH = "Select @ST =convert (VARCHAR), @N) Select @ST =rtrim (@ST) Select @L0 =len (@ST) Select @m=1while (@m<[email protected] @L0) beginselect @[email protected]+ ' 0 ' SELECT @[email protected]+1endselect @[email Protected][email Protected]return @STEND

Description: The parameter @f is the length of the variable part after the string.

2. Write a scalar-valued function that returns a primary key string Getunitsno

CREATE function Getunitsno () returns char (8) as Begindeclare @mystr char (8) Declare @str0 char (2) Declare @count intDECLARE @MaxEnd intDECLARE @st1 char (TEN) Select @str0 = ' US ' select @count =count (*) from unit_sif @count =0select @MaxEnd = 0elseselect @MaxEnd =max (substring (U#,3,len (u#)-1) from unit_s SELECT @st1 =dbo. Iconvert2string (@MaxEnd +1,6) Select @[email protected]+ LTRIM (RTRIM (@st1)) return @mystrend

Description: A stored procedure that returns a primary key string has not been implemented ....
3. Effect Display

SQL query statement: SELECT DBO. Getunitsno ()

Query Result: US000001 (case where the record in the unit_s table is empty)

4. C # frontend Call

String USNO = DB. Executesqlscalartostring ("Select DBO. Getunitsno () ");

SQL Server writes a string-type primary key that grows automatically