SQL 2000 Custom Function call GETDATE () times wrong

Source: Internet
Author: User
Tags current time getdate

When GETDATE () is invoked in a custom function in a SQL 2000 database, the result compiles the times incorrectly, prompting errors as follows: Invalid use of ' getdate ' within a function

I'll give you a test example as shown below,

--================================================================================================
--Author : Kerry
--createdate:2011-03-23-
-Description: Automatically generate serial number according to the rules
----------------------------------------- ---------------------------------------------------------
--updatedate:
------------------------------- -------------------------------------------------------------------
createprocedure usp_ Riminsamplenogenerate
@TypeVARCHAR (a)
as
    
SET NOCOUNT on;
    
Declare@samplenoasvarchar (a);
Declare@dateasvarchar (8);
Declare@serialnumberasvarchar (3)
    
    
BEGIN
    
Select@date=convert (VARCHAR), GETDATE (), 112);
--Business rules processing end go

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/

If you change the GETDATE () to dbo. GETDATE () compiles without error, but the function is invoked with an error, as follows: Object name ' dbo. GETDATE '

Invalid.

This is because you do not allow built-in nondeterministic functions in the body of a user-defined function (deterministic and nondeterministic functions you can refer to in the MSDN documentation). In SQL 2000 there is

Two solutions: Use the GETDATE () function as an argument to a custom function (as shown below) or define a view that obtains the current time to solve the problem. GETDATE () is not accurate

Fixed function. However, it seems that in SQL 2005, a custom function can call the function getdate (), as shown below, and there will be no errors when compiling and calling.

--==============================================================================================
--Author: Kerry
-createdate:2011-03-23-
Description: Automatically generate serial numbers according to the rules
------------------------------------------ ------------------------------------------------------
--updatedate:
---------------------------------- --------------------------------------------------------------
createfunction[dbo]. [Fun_generateraminisampleno] (@TypeVARCHAR (a), @DateTimeDATETIME)
Returnsvarchar () as
BEGIN
    
Declare@samplenovarchar (a);
Declare@datevarchar (8);
Declare@serialnumbervarchar (3);
    
Select@date=convert (VARCHAR (8), GETDATE (), 112); --Compilation error
--select @Date = CONVERT (VARCHAR (8), dbo. GETDATE (), 112); --Call error
--select @Date = CONVERT (VARCHAR (8), @DateTime, 112);
--Business rule end go

Author: The Hermit of Xiaoxiang

Source: http://www.cnblogs.com/kerrycode/

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.