每个site显示前10行记录(siteid是该表的一个字段)
下面的代码是部分的,请帮忙连起来:IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'dbo.proc_site_thread'
AND type = 'P')
DROP PROCEDURE dbo.proc_site_thread
GOcreate PROC dbo.proc_site_thread
AS
for (i=1;i++;i<(select max(siteid)from site))insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=\\192.168.1.5\users\sunny\导数据\check\list.xls',thread$)
()select top 10 *
from thread
where siteid=i
order by threadid
下面的代码是部分的,请帮忙连起来:IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'dbo.proc_site_thread'
AND type = 'P')
DROP PROCEDURE dbo.proc_site_thread
GOcreate PROC dbo.proc_site_thread
AS
for (i=1;i++;i<(select max(siteid)from site))insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=\\192.168.1.5\users\sunny\导数据\check\list.xls',thread$)
()select top 10 *
from thread
where siteid=i
order by threadid
:
select top 10 *
from thread
where siteid=i
order by threadid怎么使这个i生效?for (i=1;i++;i<(select max(siteid)from site))i是循环的写个存储过程
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'dbo.proc_site_thread'
AND type = 'P')
DROP PROCEDURE dbo.proc_site_thread
GOcreate PROC dbo.proc_site_thread
AS
declare @i int
set @i=1
while @i<(select max(siteid)from site)
begin
insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=\\192.168.1.5\users\sunny\导数据\check\list.xls',thread$)
(threadid)
select top 10 threadid
from thread
where siteid=@i
order by forumid
set @i=@i+1
end
go
GO
SET ANSI_NULLS ON
GOALTER PROC dbo.proc_site_thread
AS
declare @i int,
@s int
set @i=1
set @s=select max(siteid)from site
while @i<@s
begin
insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=\\192.168.1.5\users\sunny\导数据\check\list.xls',thread$)
(siteid,forumid,threadid,itemurl,firstextractiondate,latestextractiondate,subject,dateoflastreply,numofreplies,pageview,poster,dateoftopicpost,lastpostscrapedate,failed)
select top 3 siteid,forumid,threadid,itemurl,firstextractiondate,latestextractiondate,subject,dateoflastreply,numofreplies,pageview,poster,dateoftopicpost,lastpostscrapedate,failed
from thread
where siteid=@i
order by dateoflastreply desc
set @i=@i+1
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO查询出来结果是:
服务器: 消息 156,级别 15,状态 1,过程 proc_site_thread,行 7
在关键字 'select' 附近有语法错误。是怎么回事?
select top 3 siteid,forumid,threadid,itemurl,firstextractiondate,latestextractiondate,subject,dateoflastreply,numofreplies,pageview,poster,dateoftopicpost,lastpostscrapedate,failed
from thread
where siteid=5
order by dateoflastreply desc
拿出来运行是对的呀!~~
将
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOALTER PROC dbo.proc_site_thread
AS
declare @i int,
@s int
set @i=1
set @s=select max(siteid)from site
while @i<@s
begin
insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=\\192.168.1.5\users\sunny\导数据\check\list.xls',thread$)
(threadid)
select top 3 threadidfrom thread
where siteid=@i
order by dateoflastreply desc
set @i=@i+1
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO拿过来运行也是可以的呀
请高手帮忙!~~~