hint OPTION(FORCE ORDER) in user-defined function - compilation error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

hint OPTION(FORCE ORDER) in user-defined function - compilation error

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-11-13 : 08:52:30

Michael writes "How to use hint OPTION(FORCE ORDER) in user-defined function ?

create table a(i int ,val char )
create table b( i int, data char)
go
create function my_func (@v char)
returns table as
return ( select b.* from b JOIN a
ON a.id=b.id
OPTION(FORCE ORDER)
)
go
Output:
Server: Msg 156, Level 15, State 1, Procedure my_func, Line 5
Incorrect syntax near the keyword 'OPTION'.

Thanks,
Michael."

burbakei
Yak Posting Veteran

80 Posts

Posted - 2002-11-13 : 13:12:29

you can do the following:

CREATE FUNCTION my_func (@v char)
RETURNS @ForceOrder TABLE (i int, data char)
AS
BEGIN
INSERT INTO @ForceOrder
SELECT b.i, b.data
FROM b
INNER JOIN a
ON a.i = b.i
OPTION(FORCE ORDER)
RETURN
END
GO

but i couldnt underestand the meaning of the parameter of this function @v.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources