如何从 SQL Server 存储过程返回行和变量

发布于 2024-10-11 09:20:59 字数 649 浏览 2 评论 0原文

希望这很简单。我正在优化一个返回大约 500 多行的存储过程。我的计划是批量返回行，直到没有更多行可供获取。

例如，我将获取第 0-49 行，然后是 50-99 行，然后是 100-149 行，依此类推。

我使用以下 SQL 代码完成了此操作：

CREATE PROCEDURE [dbo].[mySP]  
@rowstart int,
@rowend int   

AS  

WITH MainQuery AS

(
  HUGE SELECT STATEMENT HERE
)

select * from MainQuery where row between @rowstart and @rowend

当我执行此 SP 时，我只需传入值对于 rowstart 和 rowend ，它将完美返回我想要的行范围。

问题是，我想知道每次查询后还有更多行需要获取。我认为我可以通过在 MainQuery 块完成后返回 @@ROWCOUNT 来实现这一点，但我不知道如何在每次 SP 之后获取返回的行范围和 @@ROWCOUNT 的值被执行。

当我进行返回 50 行的初始查询时，如果我知道表总共有 503 行，我可以做一些简单的数学计算 (503/50) 并计算出我还需要调用 SP 多少次。任何帮助表示赞赏！

原文

Hopefully this is simple. I'm optimizing a stored procedure that returns about 500+ rows. My plan is to return the rows in batches until there are no more rows to get.

For example, I'll get rows 0-49 -- then 50-99, then 100-149, and so on..

I've accomplished this using the following SQL code:

CREATE PROCEDURE [dbo].[mySP]  
@rowstart int,
@rowend int   

AS  

WITH MainQuery AS

(
  HUGE SELECT STATEMENT HERE
)

select * from MainQuery where row between @rowstart and @rowend

When I execute this SP, I simply pass in values for rowstart and rowend and it will return the range of rows I want perfectly.

Problem is, I want to know that there are MORE rows to get after each query. I think I can accomplish that by returning @@ROWCOUNT after the MainQuery block completes, but I don't know how to get the range of rows returned AND a value for @@ROWCOUNT after each time the SP is executed.

When I make the initial query of getting 50 rows returned, if I could know that there are 503 TOTAL table rows, I can do some simple math (503/50) and figure out how many more times I need to call the SP. Any help is appreciated!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

等数载，海棠开 2024-10-18 09:20:59

您尝试过输出参数吗？

http://msdn.microsoft.com/en -us/library/ms378108%28v=sql.90%29.aspx

回复收藏 0 原文

属性 2024-10-18 09:20:59

其他答案将告诉您如何从存储过程中获取答案...

我想指出 @@ROWCOUNT 将是 @rowend - @rowstart + 1< /代码>。因此，除了最后一页之外，还有 50 行。对于最后一页，您可以从数据表等客户端获取行数。

得到它，你需要的是这样的东西

WITH MainQuery AS
    (
  HUGE SELECT STATEMENT HERE
)
select * from MainQuery
CROSS JOIN
(SELECT COUNT(*) AS TotalRows FROM MainQuery) MC
 where row between @rowstart and @rowend

如果你想从 SQL或

WITH MainQuery AS

(
  HUGE SELECT STATEMENT HERE
)
SELECT * INTO #foo

select * from #foo F
CROSS JOIN
(SELECT COUNT(*) AS TotalRows FROM #foo) M
where F.row between @rowstart and @rowend

或中

WITH MainQuery AS

(
  HUGE SELECT STATEMENT HERE
)
SELECT * INTO #foo
select * from #foo F where F.row between @rowstart and @rowend

SELECT @outparam = COUNT(*) FROM #foo

The other answers will tell you how to get the answer out of the stored proc...

I'd like to point out that @@ROWCOUNT will be @rowend - @rowstart + 1. So, except for the last page you have 50 rows. For the last page you can get the row count in the client from, say, the DataTable.

What you need is something like this if you want it from SQL

WITH MainQuery AS
    (
  HUGE SELECT STATEMENT HERE
)
select * from MainQuery
CROSS JOIN
(SELECT COUNT(*) AS TotalRows FROM MainQuery) MC
 where row between @rowstart and @rowend

WITH MainQuery AS

(
  HUGE SELECT STATEMENT HERE
)
SELECT * INTO #foo

select * from #foo F
CROSS JOIN
(SELECT COUNT(*) AS TotalRows FROM #foo) M
where F.row between @rowstart and @rowend

WITH MainQuery AS

(
  HUGE SELECT STATEMENT HERE
)
SELECT * INTO #foo
select * from #foo F where F.row between @rowstart and @rowend

SELECT @outparam = COUNT(*) FROM #foo

回复收藏 0 原文