Silly Problem ! - SQL Server Forums

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

SQL Server Administration (2000)

Silly Problem !

Author

Topic

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2003-12-05 : 03:30:43

Hi All,

Please can u tell me why this is giving me error. I want to conditionaly fire some statement from the Inline function.

Alter function TESTFUNC(@Query smallint)
Returns table
as
Return
(
if @Query=1
Begin
Select Booking_ref from Booking_master where booking_ref=6000
End
else
begin
Select Booking_ref from Booking_master where booking_ref=5000
End
)

Thanks in Advance
Sachin Samuel

mr_mist
Grunnio

1870 Posts

Posted - 2003-12-05 : 04:40:59

What error is it giving? How are you calling the funtion? Are the permissions ok?

You can't use a single statement table return function in this manner, you would have to use a multi-statement table return function to use the IF like that, or you could re-write yours like

Alter function TESTFUNC(@Query smallint)
Returns table
as
Return
(
Select Booking_ref from Booking_master where (@query = 1 and booking_ref=6000) or
(@query <> 1 and booking_ref=5000)
)

-------
Moo. :)

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2003-12-05 : 04:58:51

Thanks mist for the prompt reply.

Below is the error which I am getting while altering the proc.

Server: Msg 156, Level 15, State 1, Procedure TESTFUNC, Line 7
Incorrect syntax near the keyword 'if'.
Server: Msg 170, Level 15, State 1, Procedure TESTFUNC, Line 15
Line 15: Incorrect syntax near ')'.

I cannot do the way u did because my select statement are from different tables, so the query getting used are different. Please help me.

Thanks in advance
Sachin Samuel

mr_mist
Grunnio

1870 Posts

Posted - 2003-12-05 : 06:43:15

quote:
I cannot do the way u did because my select statement are from different tables, so the query getting used are different. Please help me.

You see if you'd said that before then we could have saved all that time, savvy? ;)

You'll need to use the multi-statement table return type for your function in that case. Or you may find that a stored procedure is more suitable for what you are trying to achieve.

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources