Function to return table depending on IF-statement

[dirkg]

Hello,


I'm pretty new in PL SQL. What I want to do is to create a function that executes a different query depending on an IF-statement. Here's what I thought it would be:

ALTER FUNCTION dbo.fct_Person_Client_Permission_Check_2
(@PersonID int,
@ClientID int,
@SecurityLevel int)
RETURNS TABLE
AS
BEGIN
IF (SELECT [PUBLIC] FROM CLIENTS WHERE CLIENTID = @ClientID) = True
RETURN (SELECT @PersonID AS PersonID, @ClientID as ClientID, PublicSL as SecurityLevel, PublicGroups AS Groups FROM dbo.Clients WHERE (ClientID = @ClientID) )

ELSE
RETURN (SELECT PersonID, ClientID, SecurityLevel, Groups FROM dbo.Person_Client WHERE (PersonID = @PersonID) AND (ClientID = @ClientID) AND (SecurityLevel = @SecurityLevel) AND (SubscriptionStatusID = 2) )

END

However in the query analyser I get the error message "Server: Msg 170, Level 15, State 31, Procedure fct_Person_Client_Permission_Check_2, Line 14
Line 14: Incorrect syntax near 'BEGIN'." but I don't know what I'm doing wrong. I tried several different variants but nothing worked out :-s

Any help would be grately appreciated.

Grz,

Dirk

 

[SQLSister]

PL SQl is Oracle SQl. If this is qhat you are using, then I suggest you may get a better answer inthe Oracle forums.

But in SQL Server when you return a table, you generally define the structure of the table.



Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[dirkg]

I didn't know that the name wasn't PL SQL...in any case I'm using SQL Server