这样试试:CREATE PROCEDURE jhcgbm
@bj varchar(2),
@dd varchar(20)
AS
BEGINdeclare @i int
declare @s nvarchar(4000)set @s = N'SELECT @i=1 FROM '+@dd
+ N' WHERE 销售编号 LIKE '''+@bj
+ N'''+SUBSTRING(CONVERT(varchar(8),GETDATE(),112),1,8)+''%'''exec sp_executesql @s,N'@i int out',@i outif @i is not null
BEGIN
exec('SELECT CONVERT(numeric(11,0), RIGHT(MAX(销售编号), 11)) + 1 AS 单据号
FROM ' +@dd+' WHERE 销售编号
LIKE '''+@bj+'''+SUBSTRING(CONVERT(varchar(8),GETDATE(),112),1,8)+''%''')
END
ELSE
exec('SELECT TOP 1 CONVERT(numeric(11,0),CONVERT(varchar(8),GETDATE(),112)+''000'',112)+1 AS 单据号)
END
return
@bj varchar(2),
@dd varchar(20)
AS
BEGINdeclare @i int
declare @s nvarchar(4000)set @s = N'SELECT @i=1 FROM '+@dd
+ N' WHERE 销售编号 LIKE '''+@bj
+ N'''+SUBSTRING(CONVERT(varchar(8),GETDATE(),112),1,8)+''%'''exec sp_executesql @s,N'@i int out',@i outif @i is not null
BEGIN
exec('SELECT CONVERT(numeric(11,0), RIGHT(MAX(销售编号), 11)) + 1 AS 单据号
FROM ' +@dd+' WHERE 销售编号
LIKE '''+@bj+'''+SUBSTRING(CONVERT(varchar(8),GETDATE(),112),1,8)+''%''')
END
ELSE
exec('SELECT TOP 1 CONVERT(numeric(11,0),CONVERT(varchar(8),GETDATE(),112)+''000'',112)+1 AS 单据号)
END
return
这时你返回的单据号直接指定为:(也就是else子句后面那部分改一下)
CONVERT(varchar(8),GETDATE(),112)+'0001'
不必再从表里面查询获得!
然后用一个字符串变量接收并返回
---------------------------------
你表中没有数据 你指定top 1 是没有意义的
是得不到你组成的字符串值的!!!!!
@bj varchar(2),
@dd varchar(20)
AS
BEGIN
declare @i int
declare @s nvarchar(4000)
set @s = N'SELECT @i=1 FROM '+@dd
+ N' WHERE 销售编号 LIKE '''+@bj
+ N'''+SUBSTRING(CONVERT(varchar(8),GETDATE(),112),1,8)+''%'''
exec sp_executesql @s,N'@i int out',@i out if @i=1
BEGIN
exec('SELECT CONVERT(numeric(11,0), RIGHT(MAX(销售编号), 11)) + 1 AS 单据号
FROM ' +@dd+' WHERE 销售编号
LIKE '''+@bj+'''+SUBSTRING(CONVERT(varchar(8),GETDATE(),112),1,8)+''%''')
END
ELSE
exec('SELECT CONVERT(numeric(11,0),CONVERT(varchar(8),GETDATE(),112)+''000'',112)+1 AS 单据号)
END
GO