SQL Server - Assign default DateTime value to parameters in Stored Procedure
Maybe I'm being an idiot here, but I'm trying to assign a default DateTime value to a Stored Procedure in MS SQL Server 2005 but can't get it to work eg:
CREATE PROCEDURE MyProcedure
(
@MyInteger INT = 123,
@MyDateTime DATETIME = CONVERT(DATETIME, '1753-01-01 00:00:00', 20)
)
AS
BEGIN ... (snip)
So if the @MyDateTime
input parameter isn't specified, it should use '1753-01-01 00:00:00' by default, but I get the err开发者_Python百科or...
Incorrect syntax near the keyword 'CONVERT'.
I can get it to work for Integers just fine. What am I doing wrong?
Edit: from the answers below, I eventually went with the following inside the Sproc itself:
CREATE PROCEDURE MyProcedure
(
@MyInteger INT = 123,
@MyDateTime DATETIME = NULL
)
AS
BEGIN
SET NOCOUNT ON
SET @MyDateTime = COALESCE(@MyDateTime, CONVERT(DATETIME, '1753-01-01 00:00:00', 20))
...snip...
END
@MyDateTime DATETIME ='1753-01-01 00:00:00'
Or right within the sproc you can do this:
SELECT @MyDateTime = (SELECT CONVERT(DATETIME, '1753-01-01 00:00:00', 20))
The error is due to calling CONVERT, which you cannot do when defaulting parameters.
You can't have the CONVERT() function call in there, just assign the default value using the relevent datetime string.
精彩评论