inline table-valued function IF-THEN-ELSE

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 2008 Forums

Transact-SQL (2008)

inline table-valued function IF-THEN-ELSE

Author

Topic

work2gs
Starting Member

6 Posts

Posted - 2009-12-12 : 15:19:50

Hi
I define a inline table-valued function as following

ALTER FUNCTION [dbo].[GetElt](@Id int)
RETURNS TABLE
AS RETURN select name from dbo.Elt where Id=@Id

Is it possible to use if then else inside ?
Like

ALTER FUNCTION [dbo].[GetElt](@Id int)
RETURNS TABLE
AS RETURN
If @id=0
select name from dbo.Elt
else
select name from dbo.Elt where Id =@Id

thanks
Fred

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-12 : 17:10:16

Have you tried?

You can also do:
select name from dbo.Elt where Id =@Id or @Id=0

No, you're never too old to Yak'n'Roll if you're too young to die.

work2gs
Starting Member

6 Posts

Posted - 2009-12-13 : 00:50:50

Hi
Thanks a lot
is it possible to return all columns (I simplify the select...)
ALTER FUNCTION [dbo].[GetGrid](@Id int)
RETURNS @Table TABLE(*) ????
BEGIN
IF @catId=0
INSERT @Table select * from dbo.Elt
ELSE
INSERT @Table select * from dbo.Elt where catId = @catId
RETURN

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-12-16 : 15:39:15

quote:
Originally posted by work2gs

Hi
Thanks a lot
is it possible to return all columns (I simplify the select...)
ALTER FUNCTION [dbo].[GetGrid](@Id int)
RETURNS @Table TABLE(*) ????
BEGIN
IF @catId=0
INSERT @Table select * from dbo.Elt
ELSE
INSERT @Table select * from dbo.Elt where catId = @catId
RETURN

how about this?

select * from dbo.Elt where catId = coalesce(nullif(@catId ,0),catID)

An infinite universe is the ultimate cartesian product.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources