存储过程中的表名怎么用变量代替
存储过程中的表名如何用变量代替
像这里的SYSTEM_DATA_GPS_330382000001001_201112 如何用变量代替。我直接用@table 不好。
------解决方案--------------------
用动态SQL实现,
- SQL code
--降序排列 Date 开始时间 ,timeat 当前时间 CREATE procedure Data_Gps_asc @Date varchar(255), @termid varchar(255), @timeat varchar(255) as select termid,timeat,longitude,latitude from SYSTEM_DATA_GPS_330382000001001_201112 where timeat > @Date and termid = @termid and timeat<= @timeat order by timeat desc GO
像这里的SYSTEM_DATA_GPS_330382000001001_201112 如何用变量代替。我直接用@table 不好。
------解决方案--------------------
用动态SQL实现,
- SQL code
--降序排列 Date 开始时间 ,timeat 当前时间 CREATE procedure Data_Gps_asc @Date varchar(255), @termid varchar(255), @timeat varchar(255) as declare @tabname varchar(200),@sql varchar(2000) select @tabname='SYSTEM_DATA_GPS_330382000001001_201112' select @sql='select termid,timeat,longitude,latitude from '+@tabname +' where timeat>'''+@Date+''' and termid='''+@termid+''' and timeat<=''' +@timeat+''' order by timeat desc ' exec(@sql) GO
------解决方案--------------------
- SQL code
CREATE procedure Data_Gps_asc @Date varchar(255), @termid varchar(255), @timeat varchar(255) as declare @tabname varchar(200),@sql varchar(2000) select @tabname='SYSTEM_DATA_GPS_330382000001001_201112' select @sql='select termid,timeat,longitude,latitude from '+@tabname +' where timeat>'''+@Date+''' and termid='''+@termid+''' and timeat<=''' +@timeat+''' order by timeat desc ' exec(@sql) GO