Set Rowcount affecting embedded scalar function

Question

I have a SQL query that has a scalar valued function embedded in it. The function selects names from various rows from a table and concatenates them with comma as a separator. For e.g. ABC, DEF, XYZ. I have set ROWCOUNT to only 1 for the query because I want only 1 record. The problem is that if I set ROWCOUNT to 1 my scalar function yields unexpected results i.e. ABC instead of ABC, DEF, XYZ.

Can anybody help? I am restricted to use ROWCOUNT only instead of TOP clause.

Can you explain why you want to use ROWCOUNT instead of the TOP clause? — Aaron Bertrand, May 25 '13 at 16:38
Further to Aaron's comment.. @@ROWCOUNT() is a function for returning the number of rows in a result set. You cannot use ROWCOUNT to select only the TOP n rows. — twoleggedhorse, May 25 '13 at 17:30
furthermore, there is no order to a set, and SQL is set based... without an explicit ORDER BY your result value is not guaranteed. You also need to ensure that ORDER BY is deterministic, so you need a WITH TIES clause or a tiebreaker. — Volvox, May 25 '13 at 17:35
And this is why `SET ROWCOUNT` was deprecated and replaced with the TOP clause: changing it has global, unintended side-effects. — RBarryYoung, May 25 '13 at 17:54
@twoleggedhorse - You are talking about the [`@@ROWCOUNT`](http://technet.microsoft.com/en-us/library/ms187316.aspx) function not the [`SET ROWCOUNT`](http://technet.microsoft.com/en-us/library/ms188774.aspx) statement. — Martin Smith, May 25 '13 at 18:50
@RBarryYoung - AFAIK it isn't actually officially deprecated for `SELECT` queries though I don't see why they don't. — Martin Smith, May 25 '13 at 18:51
@MartinSmith Ah yes, my bad. Never used ROWCOUNT to do this sort of thing.. — twoleggedhorse, May 28 '13 at 15:36

score 0 · Accepted Answer · edited May 23 '17 at 11:43

SET ROWCOUNT will affect the rows returned by both the outer SELECT statement and the outer level of any SELECT statements contained in the scalar UDF.

So this isn't possible through use of SET ROWCOUNT alone unless you can change the definition of the Scalar UDF.

I assume that you are using the undocumented and un guaranteed variable assignment approach. If you change to the XML PATH approach then as the top most SELECT only returns 1 row anyway it will remain unaffected by the SET ROWCOUNT 1 command.

Example Code

    USE tempdb;
GO
    CREATE FUNCTION dbo.Concat1(@number INT = 0)
    RETURNS NVARCHAR(max)
    AS
      BEGIN
          DECLARE @Result NVARCHAR(max)

          SELECT @Result = COALESCE(@Result + ',','') + name
          FROM   master..spt_values
          WHERE  number = @number

          RETURN @Result
      END
GO
    CREATE FUNCTION dbo.Concat2(@number INT = 0)
    RETURNS NVARCHAR(max)
    AS
      BEGIN
          RETURN
            (SELECT STUFF((SELECT ',' + name AS [text()]
                           FROM   master..spt_values
                           WHERE  number = @number
                           FOR XML PATH('')), 1, 1, ''))
      END
GO
    SET ROWCOUNT 1;

    SELECT dbo.Concat1(number) AS Concat1,
           dbo.Concat2(number) AS Concat2
    FROM   master..spt_values
    WHERE  number = 1

    DROP FUNCTION dbo.Concat1, dbo.Concat2

Results

+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Concat1 |                                                                              Concat2                                                                               |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| rpc     | rpc,yes,autoclose,published,WINDOWS/NT,trusted,ignore duplicate keys,binary,varbinary,primary,NULL,Xact,NUL,GRANT,system table,disable_def_cnst_check,default disk |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Set Rowcount affecting embedded scalar function

1 Answers1

Example Code

Results