表名没用变量时没有错误,用了之后就提示 关键字 'set' 附近有语法错误。set ANSI_NULLS ON
ALTER PROCEDURE [dbo].[st_xyz_real]
@startTime datetime,
@EndTime datetime ,
@table varchar(50)
AS
declare @str varchar(1000)
Declare
@GPSsystime datetime,
@Gpsdate VarChar(10),
@Gpstime VarChar(10),
@x Decimal(16,4),
@y Decimal(16,4),
Delete From xg_xyz_real
declare xx cursor for
--select CAST(GPSDate AS datetime) + CAST(GPSTime AS datetime) as GPSsystime ,x,y from
--Station5011 Where cast(GPSdate as datetime) Between @StartTime and @EndTimeset @str='select CAST(GPSDate AS datetime) + CAST(GPSTime AS datetime) as GPSsystime,x,y from'
+@table+
' Where cast(GPSdate as datetime) Between @StartTime and @EndTime'
exec(@str)
open xxfetch next from xx into @GPSsystime,@x,@Y
while @@FETCH_STATUS=0
begin-- 插入数据
Insert Into xg_xyz_real Values
( @GPSsystime,@x,@Y)
fetch next from xx into @GPSsystime,@x,@Yend
close xx
deallocate xx
ALTER PROCEDURE [dbo].[st_xyz_real]
@startTime datetime,
@EndTime datetime ,
@table varchar(50)
AS
declare @str varchar(1000)
Declare
@GPSsystime datetime,
@Gpsdate VarChar(10),
@Gpstime VarChar(10),
@x Decimal(16,4),
@y Decimal(16,4),
Delete From xg_xyz_real
declare xx cursor for
--select CAST(GPSDate AS datetime) + CAST(GPSTime AS datetime) as GPSsystime ,x,y from
--Station5011 Where cast(GPSdate as datetime) Between @StartTime and @EndTimeset @str='select CAST(GPSDate AS datetime) + CAST(GPSTime AS datetime) as GPSsystime,x,y from'
+@table+
' Where cast(GPSdate as datetime) Between @StartTime and @EndTime'
exec(@str)
open xxfetch next from xx into @GPSsystime,@x,@Y
while @@FETCH_STATUS=0
begin-- 插入数据
Insert Into xg_xyz_real Values
( @GPSsystime,@x,@Y)
fetch next from xx into @GPSsystime,@x,@Yend
close xx
deallocate xx
@y Decimal(16,4),
多了一个“逗号”
+@table+
' Where cast(GPSdate as datetime) Between '+convert(varchar(20),@StartTime,120)+' and '+convert(varchar(20),@EndTime,120)
exec(@str) try
exec(@str)...
我开始用那条被注释掉的语句 没有问题,用set 之后就不行了
@startTime datetime,
@EndTime datetime ,
@table varchar(50)
AS declare @str varchar(1000)
Declare @GPSsystime datetime,
@Gpsdate VarChar(10),
@Gpstime VarChar(10),
@x Decimal(16,4),
@y Decimal(16,4)
Delete From xg_xyz_real
set @str='declare xx cursor for
select CAST(GPSDate AS datetime) + CAST(GPSTime AS datetime) as GPSsystime,x,y from '
+@table+' Where cast(GPSdate as datetime) Between '''+convert(varchar(20),@StartTime,20)+''' and '''+convert(varchar(20),@EndTime,120)+''''
exec(@str)
open xxfetch next from xx into @GPSsystime,@x,@Y
while @@FETCH_STATUS=0
begin
Insert Into xg_xyz_real Values (@GPSsystime,@x,@Y)
fetch next from xx into @GPSsystime,@x,@Yend
close xx
deallocate xx
,给你个实例declare @cSql varchar(1000),@n intset @cSql = 'declare cur cursor for ' + char(13)set @cSql = @cSql + ' select id from ta' exec(@cSql)open curfetch next from cur into @n while @@fetch_status = 0begin if @n>2
print @n
else
print 0 fetch next from cur into @n endclose cur deallocate cur
这句前面多了一个逗号2.declare xx cursor for
这里不对
select CAST(GPSDate AS datetime) + CAST(GPSTime AS datetime) as GPSsystime,x,y from '
+@table+' Where cast(GPSdate as datetime) Between '''+convert(varchar(20),@StartTime,20)+''' and '''+convert(varchar(20),@EndTime,120)+''''
exec(@str)
貌似只能这样了