存储过程exec(@sql)返回值问题

[longyangyxm]

简单代码如下,我要放到存储过程中的，目的之一是自动累加
编号，比如有HX002 那么就返回HX003,长度就5位，不过以前有老数据，所以条件加上了LEN(FCustomerCoding)=5

CREATE TABLE #TCustomerInfo
(
  FCustomerCoding varchar(10)
)
INSERT INTO #TCustomerInfo
SELECT 'HX00001'
UNION ALL SELECT 'HX001'
UNION ALL SELECT 'HX002'
UNION ALL SELECT 'ZJ001'

DECLARE @MaxNum int
DECLARE @CustomerCodingPY char(20)
SET @CustomerCodingPY='HX%'
SELECT  @MaxNum =MAX( CONVERT(int,RIGHT(FCustomerCoding,2)) )
FROM #TCustomerInfo
WHERE LEN(FCustomerCoding)=5 AND FCustomerCoding LIKE @CustomerCodingPY
--我想用@MAXNUM接收EXEC返回的值,exec是执行语句有返回值码?或者上面的查询要怎么修改
--@MAXNUM=EXEC(' SELECT  @MaxNum =MAX( CONVERT(int,RIGHT(FCustomerCoding,2)) ) FROM TCustomerInfo 
--				WHERE LEN(FCustomerCoding)=5 AND FCustomerCoding LIKE '''+@CustomerCodingPY+'%'' ')
IF @MAXNUM > 0
  BEGIN
    SELECT CASE LEN(@MAXNUM) 
		   WHEN 1 THEN  '00'+Convert(char(3),@MAXNUM+1) 
		   WHEN 2 THEN  '0'+Convert(char(3),@MAXNUM+1) 
		   WHEN 3 THEN  Convert(char(3),@MAXNUM+1) ELSE  '001' END
  END
ELSE
  BEGIN	 
	SELECT '001'
  END

DROP TABLE #TCustomerInfo

[-狙击手-]

--我想用@MAXNUM接收EXEC返回的值,exec是执行语句有返回值码?或者上面的查询要怎么修改
--@MAXNUM=EXEC(' SELECT  @MaxNum =MAX( CONVERT(int,RIGHT(FCustomerCoding,2)) ) FROM TCustomerInfo 
--  WHERE LEN(FCustomerCoding)=5 AND FCustomerCoding IKE '''+@CustomerCodingPY+'%'' ')

------------------------------------------------------------
declare @s nvarchar(1000)
set @s  = ' SELECT  @MaxNum =MAX( CONVERT(int,RIGHT(FCustomerCoding,2)) ) FROM TCustomerInfo  WHERE LEN(FCustomerCoding)=5 AND FCustomerCoding LIKE '''+@CustomerCodingPY+'%'' '

exec sp_executesql @s,N'@maxnum int output',@maxnum out

IF @MAXNUM > 0
  BEGIN
    SELECT CASE LEN(@MAXNUM) 
           WHEN 1 THEN  '00'+Convert(char(3),@MAXNUM+1) 
           WHEN 2 THEN  '0'+Convert(char(3),@MAXNUM+1) 
           WHEN 3 THEN  Convert(char(3),@MAXNUM+1) ELSE  '001' END
  END
ELSE
  BEGIN     
    SELECT '001'
  END

[sdhdy]

DECLARE @MaxNum int
DECLARE @CustomerCodingPY char(20)
SET @CustomerCodingPY='HX'
SELECT  @MaxNum =MAX( CONVERT(int,RIGHT(FCustomerCoding,2)) )
FROM #TCustomerInfo
WHERE LEN(FCustomerCoding)=5 AND FCustomerCoding LIKE '+@CustomerCodingPY+'%

[bobgu]

DECLARE @CustomerCodingPY char(20)
应该改成
DECLARE @CustomerCodingPY varchar(20)

[sdhdy]

--这样应该也可以
DECLARE @MaxNum int
DECLARE @CustomerCodingPY varchar(20)
SET @CustomerCodingPY='HX%'
SELECT  @MaxNum =MAX( CONVERT(int,RIGHT(FCustomerCoding,2)) )
FROM #TCustomerInfo
WHERE LEN(FCustomerCoding)=5 AND FCustomerCoding LIKE @CustomerCodingPY

[longyangyxm]

非常谢谢happyflystone
我自己也范了个错 把条件定义成了char类型了

[longyangyxm]

[Quote=引用 4 楼 sdhdy 的回复:]
SQL code--这样应该也可以
DECLARE @MaxNum int
DECLARE @CustomerCodingPY varchar(20)
SET @CustomerCodingPY='HX%'
SELECT @MaxNum =MAX( CONVERT(int,RIGHT(FCustomerCoding,2)) )
FROM #TCustomerInfo
WHERE LEN(FCustomerCoding)=5 AND FCustomerCoding LIKE @CustomerCodingPY
[/Quote]
是可以 不过 happyflystone 方法 我更喜欢