syntax error - 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 Development (2000)

syntax error

Author

Topic

webber123456
Starting Member

2 Posts

Posted - 2005-06-08 : 20:38:24

can someone explain solution to the following syntax error :

"The following error occured while executing the query:
Server: Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'IF'."

=========== udf ============================
create FUNCTION fn_test_yaks
(
@admin nvarchar(10),
@YearID int,
@ClientID int,
@ProjectID int
)
RETURNS TABLE
AS

RETURN

IF @admin = 'true' and @ClientID <> -1
BEGIN
SELECT *
FROM Invoices i INNER JOIN Projects p
ON i.IDProject = p.ProjectID
WHERE i.IDProject = @projectid
and year(issueDate) = @YearID
and IDuser = @ClientID
END

GO

raclede
Posting Yak Master

180 Posts

Posted - 2005-06-08 : 21:03:15

I comment out the IF stmt and it works, since the UDF is expected to RETURN a Value, just put the condition in the calling Stored Procedure and if it satisfies the condition then just call the UDF.


create FUNCTION fn_test_yaks
(
@admin nvarchar(10),
@YearID int,
@ClientID int,
@ProjectID int
)
RETURNS TABLE
AS
RETURN
--IF (@admin = 'true' and @ClientID <> -1)
--BEGIN
SELECT *
FROM Invoices i INNER JOIN Projects p
ON i.IDProject = p.ProjectID
WHERE i.IDProject = @projectid
and year(issueDate) = @YearID
and IDuser = @ClientID
--END

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-06-08 : 21:43:11

Is that how you do your job raclede ? Just keep commenting code out until the thing compiles ?

webber123456, a function that returns a table like that can't have an IF statement in it, it has to be a single select. What you can do though is put those extra conditions in the WHERE clause. Something like :

create FUNCTION fn_test_yaks
(
@admin nvarchar(10),
@YearID int,
@ClientID int,
@ProjectID int
)
RETURNS TABLE
AS

RETURN

SELECT *
FROM Invoices i INNER JOIN Projects p
ON i.IDProject = p.ProjectID
WHERE i.IDProject = @projectid
and year(issueDate) = @YearID
and IDuser = @ClientID
and @admin = 'true' and @ClientID <> -1

GO

Damian
Ita erat quando hic adveni.

raclede
Posting Yak Master

180 Posts

Posted - 2005-06-08 : 21:51:55

actually I do try to comment the code and sees if it works because this is the first time that I see a TV-UDF that has a IF statement on it. actually it doesn't work when I commented that code, since I don't have the specific table.

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-06-08 : 21:55:39

You don't get it do you ? You are answering questions you don't understand, and your method of arriving at an answer is flawed.

So, when you are at work, and you don't understand something, do you comment out until it compiles and then say "There, it's done" ?

Damian
Ita erat quando hic adveni.

webber123456
Starting Member

2 Posts

Posted - 2005-06-08 : 23:04:10

actually I need to be able to deal with conditions as described in the original posting. There is a possibility of different values in the parameters which will result in different select statements.

I only posted one option to keep it simple but there will be other options. Admin could be 'false' etc.

How can I have multiple selects in a function???

or how can I have a dynamic whereclause ??

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-06-08 : 23:19:40

Have a look in books online under CREATE FUNCTION. There are a few options there that might help you. You might look at defining the table in the returns statement, then you can use some control of flow logic to insert into the table variable.

Damian
"Foolish inconsistency is the hobgoblin of little minds." - Emerson

raclede
Posting Yak Master

180 Posts

Posted - 2005-06-09 : 00:06:27

quote:
Originally posted by Merkin

You don't get it do you ? You are answering questions you don't understand, and your method of arriving at an answer is flawed.

So, when you are at work, and you don't understand something, do you comment out until it compiles and then say "There, it's done" ?

Damian
Ita erat quando hic adveni.

When Im at work, I First do solve problems at the extend of my knowledge, and if I don't know the answer I do perform debuggin' seeing to it where the error occurs, what line number and then do debugging until it works and from that analyze and learn.. and if still I don't know the answer that's the time to do some research.

Sorry if all my answers are flawed.. and yours are always correct..I'm just a human being..

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources