Stored Procedure Default Argument Problem

[Kliot]

I'm creating a stored procedure with a few Parameters with default arguments, the problem I'm having is I can't figure out how to set the default value of one of the parameters to the current date. I thought might work @ToDate = getDate but it gives me a conversion error

Something like

ALTER PROCEDURE [dbo].[InventorySold] 
	@Code nvarchar(10),
	@FromDate datetime,

	@ToDate datetime = CurrentDate
AS
BEGIN

	SELECT   dr1.Style,  sum(dr1.Total) as Total
FROM         
   (SELECT   Wholesale.OrderDetails.STYLE, Wholesale.OrderDetails.SHIP AS Total
FROM         Wholesale.OrderDetails INNER JOIN
                      Wholesale.Orders ON Wholesale.OrderDetails.INV# = Wholesale.Orders.Inv#
WHERE     (Wholesale.OrderDetails.CODE = @Code) AND (Wholesale.Orders.ShipDate >= @FromDate) AND (Wholesale.Orders.ShipDate <= @toDate)) as dr1
group by dr1.style
RETURN @@ROWCOUNT
END

 

[SQLBill]

try using GETDATE() (note the parenthesis)

What error is it giving you?

-SQLBill

Posting advice: FAQ481-4875

 

[rac2]

What is the datatype of Wholesale.Orders.ShipDate?
Also, getdate() is a function.
And it returns a value for the current time, not just the date. For example at the moment the value represents 2007-05-16 16:13:31.076, not 2007-08-16 .
And I would write BETWEEN instead of >= and <=.
But actually I dont see where you are getting the conversion error.

CREATE PROCEDURE [dbo].[InventorySold] (
    @Code nvarchar(10),
    @FromDate datetime,

    @ToDate datetime = getdate()
)
AS
...
Wholesale.Orders.ShipDate BETWEEN @FromDate AND @toDate
...

 

[Kliot]

Thanks for the help,

If I add the parenthesis @ToDate datetime = GETDATE() I get the error "Incorrect syntax near '('."

The datatype of shipdate is datetime

The error I get is "Conversion failed when converting datetime from character string."

Thanks for the suggestion on using BETWEEN

 

[SQLDenis]

you can not use that because getdate() is a non deterministic function

do this instead

ALTER PROCEDURE [dbo].[InventorySold]
@Code nvarchar(10),
@FromDate datetime,

@ToDate datetime AS
BEGIN
if ToDate IS NULL
set ToDate = getdate()

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[SQLDenis]

you could also do this

Wholesale.Orders.ShipDate BETWEEN @FromDate AND COALESCE(@toDate,GETDATE())

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[SQLDenis]

instead of this (I forgot the @ signs before)

if @ToDate IS NULL
set @ToDate = getdate()

you can just do

set @ToDate = coalesce(@ToDate ,getdate())



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[Kliot]

Thanks all for the help, you guys are the best, I got it working.