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.
|||
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 NULLSET @.OrderGroupID = NEWID()
Euan Garden wrote: