Today, I want to write a function to get the student's admission year, and the function needs to get the current year's academic year, getdate () is required, but an error occurs during the CREATE FUNCTION;
I would like to share my experience with you:
The deterministic and side-effects functions of functions can be definite or uncertain.
If the returned results are always the same when a function is called with a specific set of input values, these functions are definite.
If the same set of specific input values are used for each function call and the returned results are always different, these functions are uncertain.
Uncertain functions produce side effects.
The side effect is to change the global status of the database, such as updating the database table or some external resources, such as files or networks (such as modifying files or sending email messages ).
Built-in uncertain functions are not allowed in the User-Defined Function body. These Uncertain functions are as follows:
@ Connections
@ Total_errors
@ Cpu_busy
@ Total_read
@ Idle
@ Total_write
@ Io_busy
@ Max_connections
@ Pack_received
@ Pack_sent
@ Packet_errors
@ Timeticks
Getdate
Getutcdate
Newid
Rand
Textptr
Although uncertain functions are not allowed in the User-Defined Function body, these user-defined functions still produce side effects when calling the extended stored procedure.
Because the extended stored procedure has a side effect on the database, it is uncertain to call the extended stored procedure function.
When you define a function call to extend the stored procedure that has database side effects, do not expect the result set to be consistent or execute the function.
Therefore, when writing a custom function, we cannot use undefined functions in the system within the function. For example, we use two methods to solve the getdate () Problem:
1) create a new view, call getdate () to get the current date, and then use the view in the function to get the current date;
2) pass getdate () into the function as a datetime parameter;