SqlServer生成字符串主键

ALTER PROCEDURE [dbo].[CreatePrimaryNo]

(

@tblName nvarchar(50),

@filedName nvarchar(50),

@prefix nvarchar(50),

@primaryNo nvarchar(50) out

) 

AS

BEGIN

declare @Ret nvarchar(4000)

declare @currday nvarchar(10)

select @currday = convert(nvarchar(8),GETDATE(),112) ;

declare @strsql nvarchar(4000);

set @Ret = '1';

if(@prefix!='')

begin

set @strsql= 'select @ret= convert(nvarchar(50),'''+@prefix+''')+ convert(nvarchar(10),'''+@currday +''')+ Right(100000000+IsNull(Right(Max('+@filedName+'),8),0)+1,8)   From '+@tblName+' where '+@filedName+' like '''+@prefix+@currday+'%''' 

end

else

begin

set @strsql= 'select @ret= convert(nvarchar(10),'''+@currday +''')+Right(100000000+IsNull(Right(Max('+@filedName+'),8),0)+1,8)   From '+@tblName+' where '+@filedName+' like '''+@currday+'%''' 

end

exec sp_executesql @strsql, N'@Ret nvarchar(50) out', @Ret out

set @primaryNo = @Ret;

END