从存储过程中选择前n行 [英] select top n rows from storedprocedure

问题描述

你好先生，

我写了一个存储过程.我正在使用联接从3个表中检索一些行.我的页面上有一个文本框.

我在该文本框中输入的值是多少，可以检索到多少行.我的存储过程是

hello sir,

I wrote a stored procedure. I am retrieving some rows from 3 tables using joins. I have a text box in the page.

What value I entered in that textbox, that much of rows can be retrieved. My stored procedure is

alter proc [dbo].[SP_FastMovingItem_Value]
	  (
	  @Location varchar(20),
	  @FromDate datetime,
	  @ToDate datetime,
	  @Rows int
	  )
	  as
	   begin
			  
			  if Exists(Select Name From Sysobjects where Name='Temp_FastMovingItem_Value')
		Begin
			Drop Table Temp_FastMovingItem_Value
		End
	 SET ROWCOUNT @Rows
 
	Select Location.LocName,MBillTran.BillTranItemCode, MenuMaster.MenuItemLnName, Sum(MBillTran.BillTranQty*MBillTran.BillTranRate) AS TotValue,
	Department.DeptName, MainGroup.MainGrpDesc into  Temp_FastMovingItem_Value
	From MBillTran, MenuMaster, Department, MainGroup,Location  
	Where MBillTran.BillTranItemCode = MenuMaster.MenuItemCode And MBillTran.BillTranDeptCode = Department.DeptCode 
	And MenuMaster.MenuDeptCode = Department.DeptCode And MenuMaster.MenuItemGrpCode = MainGroup.MainGrpCode 
	And MBillTran.BillTranExcepFlag<>'V' And MBillTran.BillTranExcepFlag<>'O' And MBillTran.BillTranUpdated='Y' 
	And MBillTran.BillTranExcepFlag='B' And MBillTran.BillTranLocCode=Location.LocCode 
	And Convert(Char(10),BillDate,111)>=CONVERT(nvarchar(20), @FromDate ,111)  And Convert(Char(10),BillDate,111)<=CONVERT(nvarchar(20), @ToDate ,111)
	And MBillTran.BillTranLocCode =(CASE @Location WHEN '0' THEN MBillTran.BillTranLocCode ELSE @Location END)
	
	GROUP BY Location.LocName,MBillTran.BillTranItemCode, MenuMaster.MenuItemLnName, MBillTran.BillTranExcepFlag, 
	Department.DeptName,  MainGroup.MainGrpDesc ORDER BY Sum(MBillTran.BillTranQty*MBillTran.BillTranRate) DESC
	
	SET ROWCOUNT 0
	select max(@Rows) from Temp_FastMovingItem_Value
		end

推荐答案

让我为您提供这种情况的总体思路.

因此，将TextBox (表示要从DB检索多少行)的值作为存储过程的参数进行传递

即

Let me give you general idea for this scenario.

For that very reason pass value of TextBox ( which states how many rows you want to retrieve from DB ) as a parameter to stored procedure

i.e.

exec dbo.Test 23

现在在接收端捕获此参数并相应地设置ROWCOUNT .

即

Now at the receiving side catch this parameter and set ROWCOUNT Accordingly.

i.e.

CREATE PROCEDURE Test
@rowscount int
AS
begin
SET ROWCOUNT @rowscount;
select * from Person.Address
end

这将返回您想要的行数.

希望有帮助.
如果有帮助，请 投票 和 接受答案 .

Which will return you desired number of rows.

Hope that helps.
Please vote and Accept Answer if it Helped.

这篇关于从存储过程中选择前n行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！