patternsqlMinor
Should I add set nocount for functions?
Viewed 0 times
fornocountshouldfunctionssetadd
Problem
I've been reading about the benefits for all triggers and procedures having "set nocount on" implemented unless you make use of DONE_IN_PROC.
I have a scalar-valued function that is called from an update trigger to calculate the sum of values across multiple tables.
Searching msdn and past articles, I haven't been able to find an explicit example of a function using set nocounton.
What is the impact on a function to include or exclude 'set nocount on'?
Is there a point or code smell in functions that should indicate the addition of set nocount on?
I have a scalar-valued function that is called from an update trigger to calculate the sum of values across multiple tables.
Searching msdn and past articles, I haven't been able to find an explicit example of a function using set nocounton.
What is the impact on a function to include or exclude 'set nocount on'?
Is there a point or code smell in functions that should indicate the addition of set nocount on?
Solution
Functions cannot return data to a client so I can't think of any use of
function_body
Specifies that a series of Transact-SQL statements, which together do not produce a side effect such as modifying a table, define the value of the function.
For example, the follow code throws an error:
Msg 443, Level 16, State 15, Procedure X_JRO_TEST, Line 5
Invalid use of a side-effecting operator 'SET OPTION ON' within a function.
SET NOCOUNT ON. If you do try to add it a function you'll get an error. SQL Server considers it to cause a side effect:function_body
Specifies that a series of Transact-SQL statements, which together do not produce a side effect such as modifying a table, define the value of the function.
For example, the follow code throws an error:
CREATE FUNCTION X_JRO_TEST()
RETURNS INT
AS
BEGIN
SET NOCOUNT ON;
RETURN 1;
END;Msg 443, Level 16, State 15, Procedure X_JRO_TEST, Line 5
Invalid use of a side-effecting operator 'SET OPTION ON' within a function.
Code Snippets
CREATE FUNCTION X_JRO_TEST()
RETURNS INT
AS
BEGIN
SET NOCOUNT ON;
RETURN 1;
END;Context
StackExchange Database Administrators Q#163353, answer score: 8
Revisions (0)
No revisions yet.