CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Vlad11
Vlad11

Reputation: 103

Stored Procedure accept optional value SQL

I have the following code -

I am trying to create a procedure which takes in the products and an OPTIONAL start data and OPTIONAL end date. At the moment these dates cannot be left out

IF OBJECT_ID(N'dbo.usp_Order') IS NOT NULL
        DROP PROCEDURE dbo.usp_Order
    GO
    CREATE PROCEDURE dbo.usp_Order
    AS
    SELECT
            o.OrderId
            o.product
            d.date

    FROM Order o

    INNER JOIN Dates d 

    ON o.orderid=d.dateid 

    WHERE "DATE" BETWEEN '09/16/2008' and '09/21/2016' 

    GO

        execute usp_Order

I have tried specifying @"date" date = null, and @"date" IS NULL in the where clause but doesnt work.

Any ideas? Thank you

Upvotes: 0

Views: 75

Answers (5)

Ambareesh Surendran
Ambareesh Surendran

Reputation: 508

Try this..

CREATE PROCEDURE dbo.usp_Order
(
    @Startdate date = null,
    @Enddate   date = null
)
AS
begin   
   SELECT
           o.OrderId
           o.product
           d.date

   FROM Order o

   INNER JOIN Dates d 

   ON o.orderid=d.dateid 

   WHERE    ([DATE] >= @Startdate  or @Startdate is null) 
        and ([DATE] <= @Enddate  or @Enddate is null) 
end
GO

Upvotes: 1

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56717

You need to take into account the case where one or both dates are null. As you have not specified whether you want to get all the records or no record then, I'll go with "get all the records":

CREATE PROCEDURE dbo.usp_Order (
    @start_date date = NULL,
    @end_date date = NULL
AS
    SELECT o.OrderId o.product,  d.date
    FROM Order o INNER JOIN
         Dates d 
         ON o.orderid = d.dateid 
    WHERE 
        d.date BETWEEN ISNULL(@start_Date, d.date) AND ISNULL(@end_Date, d.date)

Upvotes: 0

asif308
asif308

Reputation: 90

Declare Date1 Date2 like

    @Date1 date = NULL,
    @Date2 date = NULL

SELECT o.OrderId o.product d.date

FROM Order o

INNER JOIN Dates d 

ON o.orderid=d.dateid 

WHERE "DATE" BETWEEN @Date1 and @Date2

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82504

To avoid unexpected results from comparing dates to NULL, specify From date and To Date as minimum and maximum values of DateTime

CREATE PROCEDURE dbo.usp_Order (
    @FromDate Datetime = '1753-01-01' -- January 1, 1753
    @ToDate Datetime = '9999-12-31' -- December 31, 9999
)
AS
SELECT
        o.OrderId
        o.product
        d.[date]

FROM Order o

INNER JOIN Dates d 

ON o.orderid=d.dateid 

WHERE d.[date] BETWEEN @FromDate and @ToDate

GO

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

The syntax for optional parameters builds on the syntax for parameters in general. Your stored procedure has no parameters:

CREATE PROCEDURE dbo.usp_Order (
    @start_date date = NULL,
    @end_date date = NULL
AS
    SELECT o.OrderId o.product,  d.date
    FROM Order o INNER JOIN
         Dates d 
         ON o.orderid = d.dateid 
    WHERE [DATE] BETWEEN @start_date and @end_date
GO

As a note: it looks like this stored procedure would make more sense as a table valued function, but that is another matter. Also, you probably want to do something more intelligent with the default values than returning no rows; however, you don't specify what you want the defaults to be.

Upvotes: 2

Related Questions