开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜