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