日期:2014-05-18  浏览次数:20475 次

关于动态sql,exec后结果赋给变量。
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)))
运行后,把值赋给一个变量@sum,我下面要用,静态sql可以正常的使用,可是换动态后....求助擅长动态sql的同行!

------解决方案--------------------
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