SET ROWCOUNT inside of a user-defined function 
Author Message
 SET ROWCOUNT inside of a user-defined function

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



Mon, 14 Mar 2005 23:51:13 GMT
 SET ROWCOUNT inside of a user-defined function

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]


Quote:
> 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



Tue, 15 Mar 2005 14:52:26 GMT
 SET ROWCOUNT inside of a user-defined function
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
-----------------------


Quote:
> 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



Tue, 15 Mar 2005 22:23:28 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. call a user defined function from another user-defined function

2. Instrinsic function inside user-defined function?

3. HELP: User defined function inside query

4. User-defined functions inside SQL statement??

5. GETDATE() inside a user defined function???

6. return datediff function from user defined function.

7. Can't use execute function in MSSQL user-define function

8. Mixed case function or How to create user defined function

9. scalar user-defined function require the function to be owner qualified

10. recovering user defined function which is now a system function

11. Built in function =user define function

12. Instrinsic function in User-defined function


 
Powered by phpBB® Forum Software