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

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

SQL code

while(@number<[email protected])beginset @sql='SET @sum=(Convert(int,(SELECT  top 1 Mileage     FROM  [email protected] +'     WHERE GpsTime<='''+CONVERT(VARCHAR(19),@e,120)+''' AND TerminalNo='''+'02200051'+'''     ORDER BY GpsTime desc )))-(Convert(int,(SELECT  top 1 Mileage     FROM  [email protected] +'     WHERE GpsTime>='''+CONVERT(VARCHAR(19),@s,120)+''' AND TerminalNo='''+'02200051'+'''     ORDER BY GpsTime asc)))'      set @[email protected]+1        if(@sum<>0 or @sum is not null)        begin            set @[email protected][email protected]        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  [email protected] +'     WHERE GpsTime<='''+CONVERT(VARCHAR(19),@e,120)+''' AND TerminalNo='''+'02200051'+'''     ORDER BY GpsTime desc )))-(Convert(int,(SELECT  top 1 Mileage     FROM  [email protected] +'     WHERE GpsTime>='''+CONVERT(VARCHAR(19),@s,120)+''' AND TerminalNo='''+'02200051'+'''     ORDER BY GpsTime asc)))

运行后，[email protected]，我下面要用，静态sql可以正常的使用，可是换动态后....求助擅长动态sql的同行！

------解决方案--------------------

SQL code

declare @sum numeric(18,0)declare @sql nvarchar(4000)set  @sql='SET @sum=(Convert(int,(SELECT  top 1 Mileage     FROM  [email protected] +'     WHERE GpsTime<='''+CONVERT(VARCHAR(19),@e,120)+''' AND TerminalNo='''+'02200051'+'''     ORDER BY GpsTime desc )))-(Convert(int,(SELECT  top 1 Mileage     FROM  [email protected] +'     WHERE GpsTime>='''+CONVERT(VARCHAR(19),@s,120)+''' AND TerminalNo='''+'02200051'+'''     ORDER BY GpsTime asc)))'exec sp_executesql @sql,[email protected] numeric(18,0) output',@sum outputprint @sum