April 13th, 2012, 10:00 AM
Limiting query returns
Hi i currently have this a SP in SQL that looks like this.
This SP is used by a PHP webapplication which has a filter so thats why i use the or for each field. so if its blank it still displays records. Now as you can see i have limited the amount of rows being displayed to 7. My question is how would i go about removing that limit on rows if they are using the filter(i.e. using the WHERE Clause). Something added to the SP or maybe passing a limit as a variable through the PHP
CREATE proc [dbo].[PurchaseOrderDisplay] (@PurchaseOrderNumber nvarchar(50),@PurchaseOrderDate datetime ,@PurchaseOrderDate1 datetime, @AccountNumber nvarchar(50),@PartNumber nvarchar(60),@TradingPartnerID nvarchar(50)) as begin select top 7 OrderHeader.OrderID ,DocumentNumber,convert(char(12),DocumentDate,112) as DocumentDate,AccountNumber,Supplier,TradingPartnerID,OrderedProductCode from OrderHeader,OrderDetail where (@PurchaseOrderNumber = '' OR DocumentNumber = @PurchaseOrderNumber) and (@AccountNumber = '' or AccountNumber = @AccountNumber) and (@PartNumber = '' or OrderedProductCode = @PartNumber) and (@TradingPartnerID = '' or TradingPartnerID = @TradingPartnerID) and (@PurchaseOrderDate = '' or DocumentDate >= @PurchaseOrderDate and --DocumentDate<=@PurchaseOrderDate1) DocumentDate <=@PurchaseOrderDate1 or @PurchaseOrderDate1 = '') end
Any help appreciated Thanks