SET ROWCOUNT inside of a user-defined function

SET ROWCOUNT inside of a user-defined function

Post by mik » Sat, 28 Sep 2002 00:51:13



I am trying to write a user defined function to get around the SELECT

SP2

I am getting an error msg 443: Invalid use of 'UNKNOWN TOKEN' within a
function.  I am sure this is being caused by the SET ROWCOUNT because
when I comment them out, the udf compiles and runs just fine.

I am sure I can SET ROWCOUNT in the Stored Proc that will be using
this udf, but that sort of defeats the purpose of writing the function
in the first place.

Am I making a simple mistake here, or is the use of SET ROWCOUNT
invalid inside of a udf?

Here is the code for the udf:

use pubs
go

CREATE FUNCTION udf_Top_N_AuthID


BEGIN



au_id DESC

SET ROWCOUNT 0

RETURN
END
go

SELECT  * FROM pubs.dbo.udf_Top_N_AuthID(1)
go

DROP FUNCTION udf_Top_N_AuthID

GO

 
 
 

SET ROWCOUNT inside of a user-defined function

Post by Tony Rogerso » Sat, 28 Sep 2002 15:52:26


SET statements are not allowed in user defined functions, the UNKNOWN TOKEN
should return you correct information - try using another SET eg. SET
DATEFORMAT DMY for instance.

I can't think of an efficient way of doing what you want; the only way i can
think of is to run the insert into a table with an identity property on it

--
Tony Rogerson SQL Server MVP
Torver Computer Consultants Ltd
http://www.sql-server.co.uk [UK User Group, FAQ, KB's etc..]
http://www.sql-server.co.uk/tr [To Hire me]


> I am trying to write a user defined function to get around the SELECT

> SP2

> I am getting an error msg 443: Invalid use of 'UNKNOWN TOKEN' within a
> function.  I am sure this is being caused by the SET ROWCOUNT because
> when I comment them out, the udf compiles and runs just fine.

> I am sure I can SET ROWCOUNT in the Stored Proc that will be using
> this udf, but that sort of defeats the purpose of writing the function
> in the first place.

> Am I making a simple mistake here, or is the use of SET ROWCOUNT
> invalid inside of a udf?

> Here is the code for the udf:

> use pubs
> go

> CREATE FUNCTION udf_Top_N_AuthID



> BEGIN



> au_id DESC

> SET ROWCOUNT 0

> RETURN
> END
> go

> SELECT  * FROM pubs.dbo.udf_Top_N_AuthID(1)
> go

> DROP FUNCTION udf_Top_N_AuthID

> GO


 
 
 

SET ROWCOUNT inside of a user-defined function

Post by Dan Guzma » Sat, 28 Sep 2002 23:23:28


To add to Tony's response, you might consider a correlated subquery to
limit the number of rows returned.  I didn't know the purpose of the
hard-coded TOP 6 in your function so I didn't include it in this
example.

A non-clustered index on the correlated value (au_id) may help
performance.

CREATE FUNCTION udf_Top_N_AuthID


BEGIN

    SELECT [au_id]
    FROM pubs.dbo.authors a
    WHERE (SELECT COUNT(*)
        FROM pubs.dbo.authors b
        WHERE b.[au_id] >= a.[au_id])

    ORDER BY au_id DESC
    RETURN
END
GO

SELECT * FROM dbo.udf_Top_N_AuthID(1)

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy  Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------


> I am trying to write a user defined function to get around the SELECT

> SP2

> I am getting an error msg 443: Invalid use of 'UNKNOWN TOKEN' within a
> function.  I am sure this is being caused by the SET ROWCOUNT because
> when I comment them out, the udf compiles and runs just fine.

> I am sure I can SET ROWCOUNT in the Stored Proc that will be using
> this udf, but that sort of defeats the purpose of writing the function
> in the first place.

> Am I making a simple mistake here, or is the use of SET ROWCOUNT
> invalid inside of a udf?

> Here is the code for the udf:

> use pubs
> go

> CREATE FUNCTION udf_Top_N_AuthID



> BEGIN



> au_id DESC

> SET ROWCOUNT 0

> RETURN
> END
> go

> SELECT  * FROM pubs.dbo.udf_Top_N_AuthID(1)
> go

> DROP FUNCTION udf_Top_N_AuthID

> GO