sql 2000 UDF return table function

29 Jun 2006 8:56 PM

WebBuilder451

I'm trying to create a UDF that reutrns a table and i'm attempting to ise
conditional logic. So far i'm missing something. It wouldn't be the first
time. Anyway,
semi psudo code is below:
something like this
RETURN
IF @mode = 1 THEN SELECT X FROM Y WHERE x > 7
ELSE IF @mode = 2 THEN SELECT X FROM Y WHERE x <= 7
ELSE SELECT x FROM y
END

Where @mode is a parameter

--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes

29 Jun 2006 10:27 PM

Erland Sommarskog

WebBuilder451 (WebBuilder***@discussions.microsoft.com) writes:

> I'm trying to create a UDF that reutrns a table and i'm attempting to ise
> conditional logic. So far i'm missing something. It wouldn't be the first
> time. Anyway,
> semi psudo code is below:
> something like this
> RETURN
> IF @mode = 1 THEN SELECT X FROM Y WHERE x > 7
> ELSE IF @mode = 2 THEN SELECT X FROM Y WHERE x <= 7
> ELSE SELECT x FROM y
> END
>
> Where @mode is a parameter

Not knowing the tables and the actual queries you are trying to
run, it's difficult to tell if you can do this in one query.
Assuming that you can't you need to use a multi-statement function
where you say:

RETURNS @tbl TABLE (a int NOT NULL, ...)

and then insert into that return table. Tne topic on CREATE FUNCTION
in Books Online gives you more details.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

30 Jun 2006 5:06 AM

Omnibuzz

It will go something like this..

create function fn1(@mode int)
returns table
as
return (
    SELECT X FROM Y WHERE
    (@mode = 1 and x > 7 ) or
    (@mode = 2 and x <= 7) or
    (@mode not in (1,2))
    )

Its an inline table values function. You can check out the multi-lined table
valued function if you have complex logic for you select.

Hope this helps.

--
-Omnibuzz (The SQL GC)

http://omnibuzz-sql.blogspot.com/

30 Jun 2006 2:06 PM

WebBuilder451

Perfect! thanks and take the weekend off

--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes

Show quote

"Omnibuzz" wrote:

> It will go something like this..
>
> create function fn1(@mode int)
> returns table
> as
> return (
>     SELECT X FROM Y WHERE
>     (@mode = 1 and x > 7 ) or
>       (@mode = 2 and x <= 7) or
>     (@mode not in (1,2))
>     )
>
> Its an inline table values function. You can check out the multi-lined table
> valued function if you have complex logic for you select.
>
> Hope this helps.
> --
> -Omnibuzz (The SQL GC)
>
> http://omnibuzz-sql.blogspot.com/
>
>