asked on 7/24/2008

Returning records in a date ragne in Stored Proc

In the procedure below. I need help with the date ranges in the WHERE clause.
If a start is passed but no end, I also want to check the range between
the start and Now. Everything else looks OK, it's just the data ranges
I'm having problems with. Thanks for looking...

There are 946,000 records in table.

Stored Procedure:
CREATE PROCEDURE [dbo].[Select_Payments]
	@PaymentID as int,
	@PaymentCode as int,
	@PaymentDtTmStart as datetime,
	@PaymentDtTmEnd as datetime,
	@PaymentNumber as nvarchar(50),
	@PaymentAmt as decimal,
	@DepositDtTmStart as datetime,
	@DepositDtTmEnd as datetime,
	@LedgerDtTmStart as datetime,
	@LedgerDtTmEnd as datetime,
	@Void as tinyint,
	@ReceiptID as int
AS
SELECT * FROM Payments
WHERE
	 ((@PaymentID IS NULL) 
      OR (@PaymentID = 0) 
      OR (PaymentID = @PaymentID))
     AND ((@PaymentCode IS NULL) 
      OR (@PaymentCode = 0) 
      OR (PaymentCode = @PaymentCode))
     AND (PaymentDtTm BETWEEN ISNULL(@PaymentDtTmStart,'1/1/1900') AND ISNULL(@PaymentDtTmEnd,GETDATE()))
     AND ((@PaymentNumber IS NULL) 
      OR (@PaymentNumber = '*') 
      OR (PaymentNumber = @PaymentNumber))
     AND ((@PaymentAmt IS NULL) 
      OR (@PaymentAmt = 0) 
      OR (PaymentAmt = @PaymentAmt))
     AND (DepositDtTm BETWEEN ISNULL(@DepositDtTmStart, '1/1/1900') AND ISNULL(@DepositDtTmEnd,GETDATE()))
     AND (LedgerDtTm BETWEEN ISNULL(@LedgerDtTmStart, '1/1/1900') AND ISNULL(@LedgerDtTmEnd,GETDATE()))
     AND ((@Void IS NULL) 
      OR (@Void = 0) 
      OR (Void = @Void))
     AND ((@ReceiptID IS NULL) 
      OR (@ReceiptID = 0) 
      OR (ReceiptID = @ReceiptID))
     AND (@DeletedDtTm IS NULL)
GO
Test code:
DECLARE	@PaymentID as int
DECLARE	@PaymentCode as int
DECLARE	@PaymentDtTmStart as datetime
DECLARE	@PaymentDtTmEnd as datetime
DECLARE	@PaymentNumber as nvarchar(50)
DECLARE	@PaymentAmt as decimal
DECLARE	@DepositDtTmStart as datetime
DECLARE	@DepositDtTmEnd as datetime
DECLARE	@LedgerDtTmStart as datetime
DECLARE	@LedgerDtTmEnd as datetime
DECLARE	@Void as tinyint
DECLARE	@ReceiptID as int
DECLARE @DeletedDtTm as datetime
 
SET @PaymentID        = NULL
SET @PaymentCode      = NULL
SET @PaymentDtTmStart = NULL
SET @PaymentDtTmEnd   = NULL
SET @PaymentNumber    = NULL
SET @PaymentAmt       = NULL
SET @DepositDtTmStart = '1/5/2004 12:00:00 AM'
SET @DepositDtTmEnd   = '2/4/2004 12:32:52 PM'
SET @LedgerDtTmStart  = NULL
SET @LedgerDtTmEnd    = NULL
SET @Void             = NULL
SET @ReceiptID        = NULL
SET @DeletedDtTm      = NULL
 
 
SELECT * FROM ptPayments
WHERE
	 ((@PaymentID IS NULL) 
      OR (@PaymentID = 0) 
      OR (PaymentID = @PaymentID))
     AND ((@PaymentCode IS NULL) 
      OR (@PaymentCode = 0) 
      OR (PaymentCode = @PaymentCode))
     AND (PaymentDtTm BETWEEN ISNULL(@PaymentDtTmStart,'1/1/1900') AND ISNULL(@PaymentDtTmEnd,GETDATE()))
     AND ((@PaymentNumber IS NULL) 
      OR (@PaymentNumber = '*') 
      OR (PaymentNumber = @PaymentNumber))
     AND ((@PaymentAmt IS NULL) 
      OR (@PaymentAmt = 0) 
      OR (PaymentAmt = @PaymentAmt))
     AND (DepositDtTm BETWEEN ISNULL(@DepositDtTmStart, '1/1/1900') AND ISNULL(@DepositDtTmEnd,GETDATE()))
     AND (LedgerDtTm BETWEEN ISNULL(@LedgerDtTmStart, '1/1/1900') AND ISNULL(@LedgerDtTmEnd,GETDATE()))
     AND ((@Void IS NULL) 
      OR (@Void = 0) 
      OR (Void = @Void))
     AND ((@ReceiptID IS NULL) 
      OR (@ReceiptID = 0) 
      OR (ReceiptID = @ReceiptID))
     AND (@DeletedDtTm IS NULL)
GO

Open in new window

Microsoft SQL Server 2005

Last Comment

Eddie Shipman

7/28/2008

Eddie Shipman

7/24/2008

ASKER

This works but I'd like to optimize it and don't know how.

CREATE PROCEDURE [dbo].[Select_Payments]
        @PaymentID as int,
        @PaymentCode as int,
        @PaymentDtTmStart as datetime,
        @PaymentDtTmEnd as datetime,
        @PaymentNumber as nvarchar(50),
        @PaymentAmt as decimal,
        @DepositDtTmStart as datetime,
        @DepositDtTmEnd as datetime,
        @LedgerDtTmStart as datetime,
        @LedgerDtTmEnd as datetime,
        @Void as tinyint,
        @ReceiptID as int
AS
SELECT * FROM Payments
WHERE
         ((@PaymentID IS NULL) 
      OR (@PaymentID = 0) 
      OR (PaymentID = @PaymentID))
     AND ((@PaymentCode IS NULL) 
      OR (@PaymentCode = 0) 
      OR (PaymentCode = @PaymentCode))
     AND ((PaymentDtTm >= @PaymentDtTmStart) 
      OR (@PaymentDtTmStart IS NULL))
     AND ((PaymentDtTm <= @PaymentDtTmEnd) 
      OR (@PaymentDtTmEnd IS NULL))
     AND ((@PaymentNumber IS NULL) 
      OR (@PaymentNumber = '*') 
      OR (PaymentNumber = @PaymentNumber))
     AND ((@PaymentAmt IS NULL) 
      OR (@PaymentAmt = 0) 
      OR (PaymentAmt = @PaymentAmt))
     AND ((DepositDtTm >= @DepositDtTmStart) 
      OR (@DepositDtTmStart IS NULL))
     AND ((DepositDtTm <= @DepositDtTmEnd) 
      OR (@DepositDtTmEnd IS NULL))
     AND ((LedgerDtTm >= @LedgerDtTmStart) 
      OR (@LedgerDtTmStart IS NULL))
     AND ((LedgerDtTm <= @LedgerDtTmEnd) 
      OR (@LedgerDtTmEnd IS NULL))
     AND ((@Void IS NULL) 
      OR (@Void = 0) 
      OR (Void = @Void))
     AND ((@ReceiptID IS NULL) 
      OR (@ReceiptID = 0) 
      OR (ReceiptID = @ReceiptID))
     AND (DeletedDtTm IS NULL)
GO

Open in new window

Mark Wills

7/25/2008

Hi, since it is a stored procedure, use some dynamic SQL to build the sql statement and only include any parameters that are required...

ie (and have commented out the exec down the bottom - just to see the PRINT to check first, then remove the comments...)

ALTER PROCEDURE [dbo].[Select_Payments]
        @PaymentID as int,
        @PaymentCode as int,
        @PaymentDtTmStart as datetime,
        @PaymentDtTmEnd as datetime,
        @PaymentNumber as nvarchar(50),
        @PaymentAmt as decimal,
        @DepositDtTmStart as datetime,
        @DepositDtTmEnd as datetime,
        @LedgerDtTmStart as datetime,
        @LedgerDtTmEnd as datetime,
        @Void as tinyint,
        @ReceiptID as int
AS
 
declare @sql varchar(max)
 
set @sql = 'SELECT * FROM Payments
WHERE (DeletedDtTm IS NULL)' 
+ case when isnull(@paymentid,0) > 0 then ' and (PaymentID = @PaymentID) ' else '' end 
+ case when isnull(@paymentCode,0) > 0 then ' and (PaymentCode = @PaymentCode) ' else '' end
+ case when isnull(@paymentDtTmStart,'01/01/1900') > '01/01/1900' then ' and (PaymentDtTm >= @PaymentDtTmStart) ' else '' end 
+ case when isnull(@paymentDtTmEnd,'01/01/1900') > '01/01/1900' then ' and (PaymentDtTm <= @PaymentDtTmEnd) ' else '' end 
+ case when isnull(@paymentNumber,'*') <> '*' and len(rtrim(@paymentNumber)) > 0 then ' and (PaymentNumber = @PaymentNumber) ' else '' end 
+ case when isnull(@paymentAmt,0) > 0 then ' and (PaymentCode = @PaymentAmt) ' else '' end 
+ case when isnull(@depositDtTmStart,'01/01/1900') > '01/01/1900' then ' and (depositDtTm >= @depositDtTmStart) ' else '' end 
+ case when isnull(@depositDtTmEnd,'01/01/1900') > '01/01/1900' then ' and (depositDtTm <= @depositDtTmEnd) ' else '' end 
+ case when isnull(@ledgerDtTmStart,'01/01/1900') > '01/01/1900' then ' and (ledgerDtTm >= @ledgerDtTmStart) ' else '' end 
+ case when isnull(@ledgerDtTmEnd,'01/01/1900') > '01/01/1900' then ' and (ledgerDtTm <= @ledgerDtTmEnd) ' else '' end 
+ case when isnull(@void,0) > 0 then ' and (PaymentCode = @void) ' else '' end 
+ case when isnull(@receiptid,0) > 0 then ' and (PaymentCode = @receiptid) ' else '' end
 
print @sql
--exec(@sql)
 
GO

Open in new window

Eddie Shipman

7/25/2008

ASKER

In the test below, I'm getting 'Must declare the scalar variable "@PaymentID".'

Am I going to have to dynamically DECLARE my variables, they ARE inputs to the stored procedure, why would they not be in scope here?

DECLARE @PaymentID as int
DECLARE @PaymentCode  as int
DECLARE @PaymentDtTmStart as datetime
DECLARE @PaymentDtTmEnd as datetime
DECLARE @PaymentNumber nvarchar(50) 
DECLARE @PaymentAmt as decimal(18,2)
DECLARE @DepositDtTmStart as datetime
DECLARE @DepositDtTmEnd as datetime
DECLARE @LedgerDtTmStart as datetime
DECLARE @LedgerDtTmEnd as datetime
DECLARE @Void as int
DECLARE @ReceiptID as int
 
----------------------------------------
-- Set scalar values based on table data
----------------------------------------
SET  @PaymentID         = 6437941
SET  @PaymentCode       = NULL 
SET  @PaymentDtTmStart  = NULL
SET  @PaymentDtTmEnd    = NULL
SET  @PaymentNumber     = NULL
SET  @PaymentAmt        = NULL
SET  @DepositDtTmStart  = NULL
SET  @DepositDtTmEnd    = NULL
SET  @LedgerDtTmStart   = NULL
SET  @LedgerDtTmEnd     = NULL
SET  @Void              = NULL
SET  @ReceiptID         = NULL 
 
exec Select_Payments @PaymentID, @PaymentCode, @PaymentDtTmStart, @PaymentDtTmEnd, @PaymentNumber, @PaymentAmt, @DepositDtTmStart, @DepositDtTmEnd,  @LedgerDtTmStart, @LedgerDtTmEnd, @Void, @ReceiptID
 
-- Even tried this way, Same error
--exec Select_Payments 6437941,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
 
-- This is the SQL printed:
SELECT * FROM ptPayments
  WHERE (DeletedDtTm IS NULL) AND (PaymentID = @PaymentID)

Open in new window

Mark Wills

7/25/2008

That is easily fixed... wrote the procedure mainly for the PRINT so you could see the equivelent SQL.

For it to execute, need to put the values of those parameters into the @sql ...

ALTER PROCEDURE [dbo].[Select_Payments]
        @PaymentID as int,
        @PaymentCode as int,
        @PaymentDtTmStart as datetime,
        @PaymentDtTmEnd as datetime,
        @PaymentNumber as nvarchar(50),
        @PaymentAmt as decimal,
        @DepositDtTmStart as datetime,
        @DepositDtTmEnd as datetime,
        @LedgerDtTmStart as datetime,
        @LedgerDtTmEnd as datetime,
        @Void as tinyint,
        @ReceiptID as int
AS
 
declare @sql varchar(max)
 
set @sql = 'SELECT * FROM Payments
WHERE (DeletedDtTm IS NULL)' 
+ case when isnull(@paymentid,0) > 0 then ' and (PaymentID = '+convert(varchar(10),@PaymentID)+') ' else '' end 
+ case when isnull(@paymentCode,0) > 0 then ' and (PaymentCode = '+convert(varchar(10),@PaymentCode)+') ' else '' end
+ case when isnull(@paymentDtTmStart,'01/01/1900') > '01/01/1900' then ' and (PaymentDtTm >= '''+convert(varchar(20),@PaymentDtTmStart,112)+''') ' else '' end 
+ case when isnull(@paymentDtTmEnd,'01/01/1900') > '01/01/1900' then ' and (PaymentDtTm <= '''+convert(varchar(20),@PaymentDtTmEnd,112)+''') ' else '' end 
+ case when isnull(@paymentNumber,'*') <> '*' and len(rtrim(@paymentNumber)) > 0 then ' and (PaymentNumber = '+convert(varchar(10),@PaymentNumber)+') ' else '' end 
+ case when isnull(@paymentAmt,0) > 0 then ' and (PaymentCode = '+convert(varchar(10),@PaymentAmt)+') ' else '' end 
+ case when isnull(@depositDtTmStart,'01/01/1900') > '01/01/1900' then ' and (depositDtTm >= '''+convert(varchar(20),@depositDtTmStart,112)+''') ' else '' end 
+ case when isnull(@depositDtTmEnd,'01/01/1900') > '01/01/1900' then ' and (depositDtTm <= '''+convert(varchar(20),@depositDtTmEnd,112)+''') ' else '' end 
+ case when isnull(@ledgerDtTmStart,'01/01/1900') > '01/01/1900' then ' and (ledgerDtTm >= '''+convert(varchar(20),@ledgerDtTmStart,112)+''') ' else '' end 
+ case when isnull(@ledgerDtTmEnd,'01/01/1900') > '01/01/1900' then ' and (ledgerDtTm <= '''+convert(varchar(20),@ledgerDtTmEnd,112)+''') ' else '' end 
+ case when isnull(@void,0) > 0 then ' and (PaymentCode = '+convert(varchar(10),@void)+') ' else '' end 
+ case when isnull(@receiptid,0) > 0 then ' and (PaymentCode = '+convert(varchar(10),@receiptid)+') ' else '' end
 
print @sql
--exec(@sql)
 
GO

Open in new window

ASKER CERTIFIED SOLUTION

Mark Wills

7/25/2008

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Eddie Shipman

7/25/2008

ASKER

Hey, thanks a lot. We are going to modify our SP generator to do this as we have seen that the execution plans are coming way out ahead with this type of technique .vs the other.

Eddie Shipman

7/28/2008

ASKER

Hey, Mark, wanna take a look at this one:
https://www.experts-exchange.com/questions/23601504/Problems-running-Dynamic-parametized-SQL.html