Question

SQL Server存储过程里 select语句里能加入while循环查询吗?或者说怎么循环查询？

我之前用while循环里加select语句，得出来的结果出现在循环次数一样多的表里。怎么能放到同一张表里？

WHILE @StartID < @EndID
BEGIN
SELECT DX,DY,DZ,DATE
FROM GPSSTATICRESULT
WHERE ID= 1 AND GPSRESULT_DATE>=@StartDate AND GPSRESULT_DATE<=@EndDate AND
GPSRESULT_DATE IN(SELECT TOP 1 GPSRESULT_DATE FROM GPSSTATICRESULT WHERE DATEPART(wk,GPSRESULT_DATE) = DATEPART(wk, @WEEKDATE) and DATEPART(yy,GPSRESULT_DATE) = DATEPART(yy,@WEEKDATE) )
SET @WEEKDATE =DATEADD(DAY,7,@WEEKDATE)
Set @StartID = @StartID +1

Answer 1

第一种方法，创建临时表
create table #temptable（）
WHILE @StartID < @EndID
BEGIN
insert into #temptable SELECT。。。
END
第二种方法，使用拼装一个SQL
declare @sql nvarchar(2000)
WHILE @StartID < @EndID
BEGIN
组装一个合适的SQL，使用union all拼起来
set @sql=@sql+''
END
exec(@sql)

Answer 2

WHILE @StartID < @EndID
BEGIN
insert into table
SELECT DX,DY,DZ,DATE
FROM GPSSTATICRESULT
WHERE ID= 1 AND GPSRESULT_DATE>=@StartDate AND GPSRESULT_DATE<=@EndDate AND
GPSRESULT_DATE IN(SELECT TOP 1 GPSRESULT_DATE FROM GPSSTATICRESULT WHERE DATEPART(wk,GPSRESULT_DATE) = DATEPART(wk, @WEEKDATE) and DATEPART(yy,GPSRESULT_DATE) = DATEPART(yy,@WEEKDATE) )
SET @WEEKDATE =DATEADD(DAY,7,@WEEKDATE)
Set @StartID = @StartID +1
end
select * from table

追问

不行啊。。那个table定义之后还是有错误出现！