关于动态sql，exec后结果赋给变量。该如何解决
日期：2014-05-18 浏览次数：20645 次
关于动态sql，exec后结果赋给变量。
这里的sql是在while中,是动态sql,我想把
------解决方案--------------------
- SQL code
while(@number<=@result)
begin
set @sql='SET @sum=(Convert(int,(SELECT top 1 Mileage
FROM '+@GpsPath +'
WHERE GpsTime<='''+CONVERT(VARCHAR(19),@e,120)+''' AND TerminalNo='''+'02200051'+'''
ORDER BY GpsTime desc )))-(Convert(int,(SELECT top 1 Mileage
FROM '+@GpsPath +'
WHERE GpsTime>='''+CONVERT(VARCHAR(19),@s,120)+''' AND TerminalNo='''+'02200051'+'''
ORDER BY GpsTime asc)))'
set @number=@number+1
if(@sum<>0 or @sum is not null)
begin
set @Mileage=@Mileage+@sum
end
set @s=DATEADD(d,1,@s)
set @e=DATEADD(d,1,@e)
set @GpsPath='dbo.GpsPath'+CONVERT( VARCHAR(8), CAST(FLOOR(CAST(@s AS FLOAT)) AS DATETIME), 112)
end
这里的sql是在while中,是动态sql,我想把
- SQL code
=(Convert(int,(SELECT top 1 Mileage
FROM '+@GpsPath +'
WHERE GpsTime<='''+CONVERT(VARCHAR(19),@e,120)+''' AND TerminalNo='''+'02200051'+'''
ORDER BY GpsTime desc )))-(Convert(int,(SELECT top 1 Mileage
FROM '+@GpsPath +'
WHERE GpsTime>='''+CONVERT(VARCHAR(19),@s,120)+''' AND TerminalNo='''+'02200051'+'''
ORDER BY GpsTime asc)))
------解决方案--------------------
- SQL code
declare @sum numeric(18,0)
declare @sql nvarchar(4000)
set @sql='SET @sum=(Convert(int,(SELECT top 1 Mileage
FROM '+@GpsPath +'
WHERE GpsTime<='''+CONVERT(VARCHAR(19),@e,120)+''' AND TerminalNo='''+'02200051'+'''
ORDER BY GpsTime desc )))-(Convert(int,(SELECT top 1 Mileage
FROM '+@GpsPath +'
WHERE GpsTime>='''+CONVERT(VARCHAR(19),@s,120)+''' AND TerminalNo='''+'02200051'+'''
ORDER BY GpsTime asc)))'
exec sp_executesql @sql,N'@sum numeric(18,0) output',@sum output
print @sum
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
