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
>

Conditional Select Statement

Hello dbForumers,

Yet another puzzling question. I remember I saw somewhere a particular syntax to select a column based on a conditional predicate w/o using a user defined function. What I want to accomplish is this : SELECT (if column colA is empty then colB else colA) as colC from SomeTable. Possible ? Not possible? Have I hallucinated ?

Thank You!possible.
select (case colA when ='' then colB else colA end) as colC

Originally posted by Rollmops
Hello dbForumers,

Yet another puzzling question. I remember I saw somewhere a particular syntax to select a column based on a conditional predicate w/o using a user defined function. What I want to accomplish is this : SELECT (if column colA is empty then colB else colA) as colC from SomeTable. Possible ? Not possible? Have I hallucinated ?

Thank You!|||Yay, right on target.

But now I have some difficulties testing the NULL state... the syntax: ...(CASE VTE1 WHEN NULL THEN ACHN ELSE VTE1 END) AS COND_ACHN... won't throw any errors but wont work as excepted since it always sends the ELSE case no matter what...|||select isnull(vte1,achn) as COND_ACHN
or

select (CASE WHEN VTE1 is NULL THEN ACHN ELSE VTE1 END) AS COND_ACHN

Originally posted by Rollmops
Yay, right on target.

But now I have some difficulties testing the NULL state... the syntax: ...(CASE VTE1 WHEN NULL THEN ACHN ELSE VTE1 END) AS COND_ACHN... won't throw any errors but wont work as excepted since it always sends the ELSE case no matter what...|||Yay, right on target.

But now I have some difficulties testing the NULL state... the syntax: ...(CASE VTE1 WHEN NULL THEN ACHN ELSE VTE1 END) AS COND_ACHN... won't throw any errors but wont work as excepted since it always sends the ELSE case no matter what...|||To determine if an expression is NULL, use IS NULL or IS NOT NULL rather than comparison operators (such as = or !=).
follow the code of my previous message.It should work for u.

Originally posted by Rollmops
Yay, right on target.

But now I have some difficulties testing the NULL state... the syntax: ...(CASE VTE1 WHEN NULL THEN ACHN ELSE VTE1 END) AS COND_ACHN... won't throw any errors but wont work as excepted since it always sends the ELSE case no matter what...|||I just had to remove the 'VTE1' in ...(CASE VTE1... for the predicate to work accordingly =) anyways thanks a lot it works just fine now =)