Using 'IF Condition' in Table-valued 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

.NET Inside SQL Server (2005)

Using 'IF Condition' in Table-valued Functions

Author

Topic

ravurugopinath@gmail.com
Starting Member

5 Posts

Posted - 2009-03-27 : 08:10:28

When i am using If Condition in Table-valued function i am getting error like

Msg 156, Level 15, State 1, Procedure funTEST, Line 9
Incorrect syntax near the keyword 'if'.
Msg 102, Level 15, State 1, Procedure funTEST, Line 13
Incorrect syntax near ')'.

Below is my function. Can anybody please help me in syntax

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[funTEST]
(
@varCustomerId int
)
RETURNS TABLE
AS
RETURN
(
if(@varCustomerId=1)
BEGIN
select * from tblQuote
END
)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-27 : 09:05:46

[code]CREATE FUNCTION [dbo].[funTEST]
(
@varCustomerId int
)
RETURNS TABLE
AS
RETURN (
select *
from tblQuote
where @varCustomerId = 1
)
END[/code]

E 12°55'05.63"
N 56°04'39.26"

ravurugopinath@gmail.com
Starting Member

5 Posts

Posted - 2009-03-27 : 09:14:59

No my requirement is not like that

when @varCustomerId value is 1 then only i have to execute below query else i have to execute other query like below

if(varCustomerId =1)
begin
select * from tblCustomer
end
else
begin
select * from tblEmployee
end

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-27 : 09:34:01

You can write like this

CREATE FUNCTION [dbo].[funTEST] 
( 
	@varCustomerId int
)
RETURNS TABLE 
AS
RETURN	(
		select	*
		from	tblCustomer
		where	@varCustomerId = 1

		union all

		select	*
		from	tblEmployee
		where	@varCustomerId <> 1
	)

but you have to make sure all columns have the same datatype, otherwise they are converted (if possible or you get an error).
But why do you want to have this horrible approach?

E 12°55'05.63"
N 56°04'39.26"

ravurugopinath@gmail.com
Starting Member

5 Posts

Posted - 2009-03-27 : 09:56:22

Dear Peso,

Thank you for your quick response.

But my requirement is not like that. Let me explain clealy.

I am passing a flag value which will store either 0 or 1 .

Then when flag value is 1 then i need to execute a query and when flag value is 0 then i need to execute other query

i.e for example

if(@flag=1)
begin
select * from tblCustomer
end

if(@flag=0)
begin
select * from tblEmployee
end

the two tables does not contains any flag column field.

One more thing i have done like this in stored procedures but why not i am not able to do this functions?

Really i am confused tried many times. I am calling functions inside the stored procedures.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources