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 likeMsg 156, Level 15, State 1, Procedure funTEST, Line 9Incorrect syntax near the keyword 'if'.Msg 102, Level 15, State 1, Procedure funTEST, Line 13Incorrect syntax near ')'.Below is my function. Can anybody please help me in syntaxset ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOCREATE FUNCTION [dbo].[funTEST] ( @varCustomerId int)RETURNS TABLE ASRETURN ( 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 ASRETURN ( 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 thatwhen @varCustomerId value is 1 then only i have to execute below query else i have to execute other query like belowif(varCustomerId =1) begin select * from tblCustomer endelse 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 ASRETURN ( 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 exampleif(@flag=1) begin select * from tblCustomer endif(@flag=0) begin select * from tblEmployee endthe 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. |
 |
|
|
|
|