存储过程中的表名怎么用变量代替

存储过程中的表名如何用变量代替

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 descGO

像这里的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)asdeclare @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)asdeclare @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 

 

查看更多 下一篇