寫函數的時候遇到了這樣的問題
使用
raiserror('enter this line', 16, -1) with log
print('enter this line')
分別遇到了
Invalid use of a side-effecting operator 'Print' within a function
Invalid use of a side-effecting operator 'Raiserror' within a function
原來，在函數中使用DML還真不能太隨意，SELECT可以；UPDATE/DELTE/INSERT還真不可以。
網上google了下，還真是不少類似的案例
1. Functions are used to return data: scalar-value or table-value.
2.
Invalid use of side-effecting or time-dependent operator in ‘newid’ within a function.
It states that the use of side-effecting and time-dependent operators is not allowed within a function.
Side-Effecting Operators
A side-effecting operator is basically what it says.
It is an operator that affects anything outside the function.
This could be seen when trying to create an object or insert or update a table.
Functions should be self-contained.
They can use data from the database, but should not affect data in the database.
In other words, functions should be read-only.
There is a list of side-effecting operators, which include INSERT, CREATE, UPDATE, OPEN, CLOSE, DELETE, SELECT … INTO, and more.
You cannot use these inside a function.
If you do, you will receive a slightly different message. The message will not mention time-dependent operators. It will look like this.
Msg 443, Level 16, State 15, Procedure FN_TEST, Line 9
Invalid use of a side-effecting operator ‘INSERT’ within a function.
To get around some of the side-effecting operator rules shown by Msg 443, we can use a table variable.
Below is an example on a table valued function. It returns a table variable.
In the function we insert, update, and delete from the table variable.
You cannot create a temp table, but table variables are OK.
CREATE FUNCTION FN_TEST()
RETURNS @table TABLE (
i int,
j int)
AS
BEGIN
INSERT INTO @table
SELECT 1, 0
UNION ALL
SELECT 2, 2
UPDATE @table
SET j = 1
DELETE @table
WHERE i = 1
RETURN
END
If you try this function yourself, you will see that it compiles and executes with no problem.
Operators used on table variables are not considered side-effecting operators.
Time-Dependent Operators
You will also receive Msg 443 if you use time-dependent operators in a function.
Time-dependent operators are those which return a value based on the time.
This include, but are not limited to GETDATE(), SYSDATETIME(), NEWID(), and RAND().
This is because functions should to return the same value when provided with the same set of inputs.
Since these functions return a different value each time, they are not allowed.
There is one solution to part of this problem. If you wish to use a date or an id, you can provide it to the function.
You could pass NEWID() or GETDATE() as the value of a parameter sent to the function.
If you need to use time-dependent operators multiple times, you can’t use a function.