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