Can not call SET ROWCOUNT in a UDF - Example

I forgot to supply an example. The following is a minimal function that
gives the error
CREATE FUNCTION KUKU()
RETURNS INT
BEGIN
SET ROWCOUNT 1
return 1
END
"Boaz Ben-Porat" <bbp@.milestone.dk> wrote in message
news:O2BhOfTFGHA.3200@.tk2msftngp13.phx.gbl...
> When I call SET ROWCOUNT <number> inside a User Defined Function I get the
> following error:
> Error 443: Invalid use of 'UNKNOWN TOKEN' within a function.
> I couldn't find anything about this error in the BOL.
> Any solution ?
> TIA
> Boaz Ben-Porat
> Milestone systems
> Debmark
>A scalar UDF, by definition, returns exactly one "row", so what do you need
to override it for, and what do you consider to be a "row"? This returns a
single value... there are no rows or columns...
"Boaz Ben-Porat" <bbp@.milestone.dk> wrote in message
news:eqhochTFGHA.216@.TK2MSFTNGP15.phx.gbl...
>I forgot to supply an example. The following is a minimal function that
>gives the error
> CREATE FUNCTION KUKU()
> RETURNS INT
> BEGIN
> SET ROWCOUNT 1
> return 1
> END
>
>
> "Boaz Ben-Porat" <bbp@.milestone.dk> wrote in message
> news:O2BhOfTFGHA.3200@.tk2msftngp13.phx.gbl...
>

Can not call SET ROWCOUNT in a UDF

When I call SET ROWCOUNT <number> inside a User Defined Function I get the
following error:
Error 443: Invalid use of 'UNKNOWN TOKEN' within a function.
I couldn't find anything about this error in the BOL.
Any solution ?
TIA
Boaz Ben-Porat
Milestone systems
DebmarkUse the SET directive before calling the function?
SET NOCOUNT ON
SELECT * FROM dbo.function()
?
"Boaz Ben-Porat" <bbp@.milestone.dk> wrote in message
news:O2BhOfTFGHA.3200@.tk2msftngp13.phx.gbl...
> When I call SET ROWCOUNT <number> inside a User Defined Function I get the
> following error:
> Error 443: Invalid use of 'UNKNOWN TOKEN' within a function.
> I couldn't find anything about this error in the BOL.
> Any solution ?
> TIA
> Boaz Ben-Porat
> Milestone systems
> Debmark
>|||Seems that SET ROWCOUNT isn't allowed in a UDF.
If you're using QA with syntax coloring, note that ROWCOUNT is not
colored as a keyword, so that would be why the 'UNKNOWN TOKEN' is being
returned.
Boaz Ben-Porat wrote:
> When I call SET ROWCOUNT <number> inside a User Defined Function I get the
> following error:
> Error 443: Invalid use of 'UNKNOWN TOKEN' within a function.
> I couldn't find anything about this error in the BOL.
> Any solution ?
> TIA
> Boaz Ben-Porat
> Milestone systems
> Debmark
>|||correction: SET <OPTION> ON is not allowed in a UDF. (set rowcount, set
nocount, etc.)
Trey Walpole wrote:
> Seems that SET ROWCOUNT isn't allowed in a UDF.
> If you're using QA with syntax coloring, note that ROWCOUNT is not
> colored as a keyword, so that would be why the 'UNKNOWN TOKEN' is being
> returned.
> Boaz Ben-Porat wrote:
>|||Hello, Boaz
You cannot use SET ROWCOUNT in a UDF, because a function cannot contain
statements that cause side-effects. If you wish to use a constant for
SET ROWCOUNT, you can use the "TOP n" clause, instead. If you want to
use a variable (or a parameter) you can use the "TOP (n)" clause in SQL
Server 2005 (but I understand that you use SQL Server 2000). If you
cannot upgrade to SQL Server 2005, it may be possible to use a
subquery, but it will be slower (you should consider using a stored
procedure instead of a function).
Razvan