关于动态sql，exec后结果赋给变量。该如何解决

关于动态sql，exec后结果赋给变量。

SQL code

while(@number<=@result)beginset @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 outputprint @sum 

 

查看更多 下一篇