conditional syntax in functions

I can't seem to get the nesting correct for an IF THEN condition inside a
function. My intent is to return the results (a table) of one of two
dfferent complex select statements. And, really, I am porting this SELECT
over from a working stored procedure. I wanted the convenience of being able
to use it in another select statement to further limit it down without
filters.
I keep getting "Incorrect syntax near 'BEGIN'"
To summarize:
CREATE FUNCTION dbo.fnGetProducts
( @.category int = 1,
@.subcategory int = 1,
@.classification int = 0)
RETURNS table
AS
BEGIN
If @.category=@.subcategory
RETURN (
SELECT ...... WHERE products.FK_category = @.category
)
ELSE
RETURN (
SELECT ...... WHERE products.FK_category = @.category
AND products.FK_subcategory = @.subcategory
)
END
GO
Am I doing this correctly?
Thanks
JulianYou are mixing inline table-valued functions (which are basically views that
accept parameters) and multi-statement table-valued functions (which allow
control-flow statement like IF..ELSE
Try the following to have an inline table-valued function:
CREATE FUNCTION dbo.fnGetProducts
( @.category int = 1,
@.subcategory int = 1,
@.classification int = 0)
RETURNS table
AS
RETURN (
SELECT ...... WHERE products.FK_category = @.category
AND products.FK_subcategory = CASE WHEN
@.category=@.subcategory
THEN products.FK_subcategory ELSE @.subcategory
END
)
END
GO
Jacco Schalkwijk
SQL Server MVP
"stjulian" <anonymous@.discussions.microsoft.com> wrote in message
news:eUH9CyHYFHA.3712@.TK2MSFTNGP09.phx.gbl...
>I can't seem to get the nesting correct for an IF THEN condition inside a
>function. My intent is to return the results (a table) of one of two
>dfferent complex select statements. And, really, I am porting this SELECT
>over from a working stored procedure. I wanted the convenience of being
>able to use it in another select statement to further limit it down without
>filters.
> I keep getting "Incorrect syntax near 'BEGIN'"
> To summarize:
> CREATE FUNCTION dbo.fnGetProducts
> ( @.category int = 1,
> @.subcategory int = 1,
> @.classification int = 0)
> RETURNS table
> AS
> BEGIN
> If @.category=@.subcategory
> RETURN (
> SELECT ...... WHERE products.FK_category = @.category
> )
> ELSE
> RETURN (
> SELECT ...... WHERE products.FK_category = @.category
> AND products.FK_subcategory = @.subcategory
> )
> END
> GO
>
>
> Am I doing this correctly?
>
> Thanks
> Julian
>|||If the in-line function would not work for you (because the two select
statements are completely different), you may want to use an
multi-statement function, i.e. something like this:
CREATE FUNCTION dbo.fnGetProducts
( @.category int = 1,
@.subcategory int = 1,
@.classification int = 0)
RETURNS @.result TABLE (
column1 int,
column2 varchar(50),
..
)
AS
BEGIN
IF @.category=@.subcategory BEGIN
INSERT INTO @.result (column1, column2, ...)
SELECT .... WHERE products.FK_category = @.category
END
ELSE BEGIN
INSERT INTO @.result (column1, column2, ...)
SELECT .... WHERE products.FK_category = @.category
AND products.FK_subcategory = @.subcategory
END
RETURN
END
GO
Of course, if the two SELECT statements are similar, it's easier (and
usually better) to write an in-line function, like Jacco suggested.
Razvan|||If you just have 2 select statement in your function, you can always write
it as an inline function. The two select statements must always return the
same columns when you have a multi-statement function, so you can always put
them in an inline function with a UNION. Inline functions have less overhead
and in general leas to better query plans.
Jacco Schalkwijk
SQL Server MVP
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1116961197.756965.191640@.g49g2000cwa.googlegroups.com...
> If the in-line function would not work for you (because the two select
> statements are completely different), you may want to use an
> multi-statement function, i.e. something like this:
> CREATE FUNCTION dbo.fnGetProducts
> ( @.category int = 1,
> @.subcategory int = 1,
> @.classification int = 0)
> RETURNS @.result TABLE (
> column1 int,
> column2 varchar(50),
> ...
> )
> AS
> BEGIN
> IF @.category=@.subcategory BEGIN
> INSERT INTO @.result (column1, column2, ...)
> SELECT .... WHERE products.FK_category = @.category
> END
> ELSE BEGIN
> INSERT INTO @.result (column1, column2, ...)
> SELECT .... WHERE products.FK_category = @.category
> AND products.FK_subcategory = @.subcategory
> END
> RETURN
> END
> GO
> Of course, if the two SELECT statements are similar, it's easier (and
> usually better) to write an in-line function, like Jacco suggested.
> Razvan
>