Default DATE and uniqueidentifier parameters?

I have several stored procedures and to facilitate getting the output of the stored procedures we have been adding default values for all of the input parameters. This works fine with the exception of DATE and uniqueidentifier parameters. I have defined stored procedures like:

ALTER PROCEDURE [dbo].[proc_GetOrderReasonByOrderGroupId]

@.OrderGroupId uniqueidentifier = NEWID

AS

and

ALTER PROCEDURE [dbo].[proc_shippedPackages]

@.DateFrom datetime = GETDATE,

@.DateTo datetime = GETDATE

but when I execute the following

SET FMTONLY ON

exec proc_shippedPackages

SET FMTONLY OFF

I get

Msg 241, Level 16, State 1, Procedure proc_shippedPackages, Line 0

Conversion failed when converting datetime from character string.

Any suggestions? The same error occurs with setting a uniqueidentifier. I want to create a default parameter that will more of less ensure that the output is empty.

Thank you.

Kevin

You could try the example below.

Beware, though, that if you do need to explicitly set the values of the @.DateFrom and @.DateTo parameters to NULL when calling the stored procedure [as opposed to simply not providing values for these optional parameters] then the parameters will be assigned a value of GETDATE() during execution, which may or may not be what you want. If this does turn out to be a problem then you could use an arbitrary [but unlikely to be used] date as the default value and then check for that value rather than NULL when assigning the value of @.Now.

Chris

Code Snippet

CREATE PROCEDURE [dbo].[proc_shippedPackages]

@.DateFrom DATETIME = NULL,

@.DateTo DATETIME = NULL

AS

--Ensure that both variables are set to

--equal values if defaults are required.

DECLARE @.Now DATETIME

SET @.Now = GETDATE()

IF @.DateFrom IS NULL

BEGIN

SET @.DateFrom = @.Now

END

IF @.DateTo IS NULL

BEGIN

SET @.DateTo = @.Now

END

SELECT @.DateFrom, @.DateTo

GO

|||

It is not bad idea to have NON-NULL values, suppose if you need to store / pass the NULL value from your code the below code wont break.

Code Snippet

Alter PROCEDURE [dbo].[proc_GetOrderReasonByOrderGroupId]

@.OrderGroupId uniqueidentifier = 0x0

AS

Select@.OrderGroupId = Case When @.OrderGroupId = 0x0 Then NewId() Else @.OrderGroupId End

go

Alter PROCEDURE [dbo].[proc_shippedPackages]

@.DateFrom datetime = '1900-01-01 00:00:00.000',

@.DateTo datetime = '1900-01-01 00:00:00.000'

as

SET @.DateFrom = Case When @.DateFrom = '1900-01-01 00:00:00.000' Then GetDate() Else @.DateFrom End

SET @.DateTo = Case When @.DateTo = '1900-01-01 00:00:00.000' Then GetDate() Else @.DateTo End

|||GETDATE and NEWID are functions and require () after them, unlike VB. Try:

@.OrderGroupId uniqueidentifier = NEWID()

@.DateFrom datetime = GETDATE(),

@.DateTo datetime = GETDATE()

|||TPhillips -> You are wrong; SP params only support the constant/NULL value as default value.|||Yes, you are correct. Your method, mentioned earlier, is the way to fix this problem.

However, the () are still needed to execute the functions.

|||

Tom Phillips wrote:

Yes, you are correct. Your method, mentioned earlier, is the way to fix this problem.

However, the () are still needed to execute the functions.

If you check the syntax with the Sql Management Studio it complains if you add the ().

|||

Kevin,

You cannot use the NEWID() and GETDATE() functions as default values in the parameter definition.

DEFAULT value assignments must be deterministic. Non-deterministic functions are not permitted in that context.

If your intent is to make the parameters optional, use '01/01/1900' (or NULL), then in the first lines of the sproc, check the values and if = '01/01/1900' (or NULL), then set the values = getdate().

Your original attempt failed because you are setting the default values to the string constants 'GETDATE' and 'NEWID.

NEWID() and GETDATE() both require parentheses as previously mentioned.

|||

Tom Phillips wrote:

Yes, you are correct. Your method, mentioned earlier, is the way to fix this problem.

However, the () are still needed to execute the functions.

When I include the () I get:

Msg 102, Level 15, State 1, Procedure proc_GetCaseNotesByOrderGroupID, Line 4

Incorrect syntax near '('.

ALTER PROCEDURE [dbo].[proc_GetCaseNotesByOrderGroupID]

@.OrderGroupID uniqueidentifier = NEWID()

AS

|||As mentioned, you have to set the default to a "static", you cannot use a function on a default value.

What your code was doing without the () is equivalent to:

@.OrderGroupID uniqueidentifier = 'NEWID'

I assume you did not want the @.orderGroupID to be a string NEWID. I think this is a bug or at least hold over from Sybase which allows unquoted strings to be invisibly converted to a string.

The best way to do what you want is:

ALTER PROCEDURE [dbo].[proc_GetCaseNotesByOrderGroupID]

@.OrderGroupID uniqueidentifier = NULL -- or some other non-occurring number

AS

IF @.OrderGroupID IS NULL
SET @.OrderGroupID = NEWID()

sql