--我想用@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 outIF @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
--@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 outIF @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
解决方案 »
- 系统对象的object_id是不是小于某个值呢?
- 没有题,郁闷,关于AS附近有语法错误问题
- mssql里面有没有 类似与vb的 select case 变量 case xx case xx1 的语法
- Like用法——高手们帮忙啊
- 关于Sql server NextRecordset()出错的问题
- 请问我想查询第5到8条数据该如何实现?
- SQL查找兩個數據表
- 请问如何打开.db文件?
- 高手帮忙啊,必有重谢(300分)
- 请问那里可以找到vfp的反编译软件?
- 【向高手们提问】:复杂sql语句,我写了实现方法,但是没有达到预期效果,希望高手们指点
- 系统表sysprocesses在客户端异常退出时出现的问题
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+'%
应该改成
DECLARE @CustomerCodingPY varchar(20)
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
我自己也范了个错 把条件定义成了char类型了