3

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?

6

Functions cannot return data to a client so I can't think of any use of 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.

  • Can a function select records and return @@ROWCOUNT? – McNets Feb 6 '17 at 15:11
  • 1
    @McNets I don't think that a scalar-valued function can, but SET NOCOUNT ON doesn't prevent @@ROWCOUNT from working. – Joe Obbish Feb 6 '17 at 15:15
  • 1
    Yes, you're right. – McNets Feb 6 '17 at 15:23
  • @JoeObbish, Thanks :), after all that research, I'm disappointed in myself that I didn't just try it. :P – Gander7 Feb 7 '17 at 11:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.