Usage of IF statements inside SQL functions

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

Transact-SQL (2005)

Usage of IF statements inside SQL functions

Author

Topic

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-07-07 : 06:43:25

Hello
Im getting an error when i try to use the following sql statement in a SQL function.



CREATE FUNCTION [dbo].[fn_MyFunc](@flag as bit)
RETURNS varchar(20)
AS
BEGIN
RETURN (
If @flag = 0
    SELECT 'Zero'          
Else   
    SELECT 'NotZero'
      )
END

The error message says :
Incorrect syntax near the keyword 'IF'.
Please point me out where is that i am going wrong.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-07 : 06:53:04

[code]
CREATE FUNCTION [dbo].[fn_MyFunc](@flag as bit)
RETURNS varchar(20)
AS
BEGIN
RETURN
(
case
when @flag = 0 then
'Zero'
Else
'NotZero'
end
)
END
[/code]

KH
[spoiler]Time is always against us[/spoiler]

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-07-07 : 06:57:53

Thanks for your reply
Actually i want to use SELECT statements inside the conditions.
Is that possible ? Since i get errors if i include the select statements inside the case statement.

Regards
Srivatsa

quote:

Originally posted by khtan


CREATE FUNCTION [dbo].[fn_MyFunc](@flag as bit)
RETURNS varchar(20)
AS
BEGIN
RETURN 
(
	case 
	when 	@flag = 0 then
    		'Zero'          
	Else   
    		'NotZero'
	end
)
END

KH
[spoiler]Time is always against us[/spoiler]

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-07-07 : 07:31:22

A small mistake from my side ...
The query should return a table instead of a text

quote:

Originally posted by khtan


CREATE FUNCTION [dbo].[fn_MyFunc](@flag as bit)
RETURNS TABLE
AS
BEGIN
RETURN 
(
      IF @flag = 0 
            SELECT 'ZER0'
      ELSE 
            SELECT 'NOT ZER0'
  
)
END

KH
[spoiler]Time is always against us[/spoiler]

[/quote]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-07 : 08:00:25

CREATE FUNCTION [dbo].[fn_MyFunc](@flag as bit)
RETURNS @temp TABLE ( flag varchar(20))
AS
BEGIN

insert into @temp
SELECT case when @flag = 0 then 'Zero'
Else 'NotZero'end

RETURN

END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-07 : 08:12:35

[code]
CREATE FUNCTION [dbo].[fn_MyFunc](@flag AS bit)
RETURNS TABLE
AS
RETURN
(
SELECT
CASE
WHEN @flag = 0 THEN
'Zero'
ELSE
'NotZero'
END AS flag
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-07-07 : 09:23:52

Many thanks for your solution.
This works .. !!!
Also i wanted to ask whether this will work for
the case if i want to use the select statements between two tables.

i.e The function fn_MyFunc should return the columns of the results of two different tables

CREATE FUNCTION [dbo].[fn_MyFunc](@flag AS bit)
RETURNS TABLE
AS
RETURN
(
SELECT
CASE
WHEN @flag = 0 THEN
Select COL1,COL2 from Table1
ELSE
Select COL1,COL2 from Table2
END AS flag
)

???
???
How to overcome in such scenarios ??

Regards
Srivatsa

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-07 : 09:44:22

[code]CREATE FUNCTION [dbo].[fn_MyFunc](@flag AS bit)
RETURNS TABLE
AS
RETURN
(
SELECT COL1, COL2 FROM Table1 WHERE @flag = 0
UNION ALL
SELECT COL1, COL2 FROM Table2 WHERE @flag <> 0
)[/code]

KH
[spoiler]Time is always against us[/spoiler]

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-07-07 : 10:17:51

Thanks a ton Khtan !!!

You saved my day

quote:

Originally posted by khtan

CREATE FUNCTION [dbo].[fn_MyFunc](@flag AS bit)
RETURNS TABLE
AS
RETURN 
(
	SELECT  COL1, COL2 FROM Table1 WHERE @flag = 0
	UNION ALL
	SELECT  COL1, COL2 FROM Table2 WHERE @flag <> 0
)

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources