1、编写标量值函数ICONVERT2STRING

CREATE FUNCTION ICONVERT2STRING(@N INT,@F INT) RETURNS VARCHAR(10) AS
BEGIN
DECLARE @M INT
DECLARE @L0 INT
DECLARE @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=1
WHILE (@M<=@F-@L0)
	BEGIN
	SELECT @CH=@CH+'0'
	SELECT @M=@M+1
	END
SELECT @ST=@CH+@ST
RETURN @ST
END

2、编写返回主键字符串的标量值函数GetUnitSNo

CREATE function GetUnitSNo() returns char(8) as 
begin
Declare @mystr char(8)
Declare @str0 char(2)
Declare @count int
Declare @MaxEnd int
Declare @st1 char(10)
select @str0='US'
select @count=count(*) from Unit_S
if @count=0
	select @MaxEnd=0
else
	select @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 @mystr
end

    SQL查询语句：SELECT DBO.GetUnitSNo()

    查询结果：US000001（Unit_S表中的记录为空的情况）

4、C#前端调用

    string USNO = DB.ExecuteSQLScalarTOstring("SELECT DBO.GETUNITSNO()");

SQL Server 编写自动增长的字符串型主键