Incorrect Syntax Near Keyword 'THEN'

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

General SQL Server Forums

New to SQL Server Programming

Incorrect Syntax Near Keyword 'THEN'

Author

Topic

gp_330
Starting Member

6 Posts

Posted - 2005-08-09 : 18:05:53

Hi Guys, I am new to SQL IF THEN Statements, I am having problems getting the code below to check out in my SQL Syntax checked.

I would really appreciate some assistance from anyone because I am sure that the answer is really simple.

CREATE PROCEDURE CheckCan2
@Position1 varchar(150),
@Position2 varchar(150),
@Position3 varchar(150),
@userid int

AS

BEGIN TRAN

If (@Position1 <> '') THEN
SELECT jobs.id, jobs.startdate, jobs.title, jobs.company, jobs.startdate, jobs.duration, jobs.salary, jobs.dateadded, jobs.city, jobs.country FROM jobs, details, seekers WHERE seekers.position=jobs.position AND seekers.userid=@userid
GO
ElseIf (@Position1 <> '') AND (@Position2 <> '') THEN
SELECT jobs.id, jobs.startdate, jobs.title, jobs.company, jobs.startdate, jobs.duration, jobs.salary, jobs.dateadded, jobs.city, jobs.country FROM jobs, details, seekers WHERE (details.discipline2=jobs.position AND details.userid=@userid) OR (seekers.position=jobs.position AND seekers.userid=@userid)
GO
ElseIf (@Position1 <> '') AND (@Position2 <> '') AND (@Position3 <> '') THEN
SELECT jobs.id, jobs.startdate, jobs.title, jobs.company, jobs.startdate, jobs.duration, jobs.salary, jobs.dateadded, jobs.city, jobs.country FROM jobs, details, seekers WHERE ((details.discipline2=jobs.position OR details.discipline3=jobs.position) AND details.userid=@userid) OR (seekers.position=jobs.position AND seekers.userid=@userid)
GO
END IF
END TRAN

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-08-09 : 18:31:44

Hi there.

You should review the Control-of-Flow Language sections of Books Online. Your syntax is just a little off (no THEN, ELSEIF).

BOL: [url]mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsqlref.chm::/ts_ca-co_0tnp.htm[/url]

For example, your first clause would be written like this:

If (@Position1 <> '') 
BEGIN
	SELECT 	j.[id], 
		j.startdate, 
		j.title, 
		j.company, 
		j.startdate, 
		j.duration, 
		j.salary, 
		j.dateadded, 
		j.city, 
		j.country 
	FROM jobs j inner join seekers s
		on j.position = s.position
	WHERE s.userid = @userid
END

(I updated your query using newer join syntax and aliases to ease reading. I also removed your reference to table [details] because it was not being used anywhere in the query.)

Also, is there a reason why you are using a transaction? You are only selecting data, not altering anything, correct?

END TRAN is not valid.

Nathan Skerl

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-08-09 : 18:48:05

Im thinking you should just post the structures for the 3 tables referenced with some sample data and desired results.

Lets start there

Look here for some guidlines:
[url]http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx[/url]

Nathan Skerl

Kristen
Test

22859 Posts

Posted - 2005-08-09 : 22:07:39

Hi gp_330, Welcome to SQL Team!

Remove the THEN from the IF statements

Change the ElseIf to ELSE IF

Remove the GO within the procedure

Remove the END IF

As there are only SELECT statements the Transaction Block is not needed

You may want to put
BEGIN
...
END
around each block within each IF statement - there is only one statement at the moment, so you are OK just now

I don't think you are going to get the desired result


	FROM	jobs, details, seekers 
	WHERE	    seekers.position=jobs.position 
		AND seekers.userid=@userid

is going to JOIN "jobs" and "seekers" and then join EVERY row in that result set to every row in "details" (cartesian join)

That said you are ONLY selecting columns from the "jobs" table, so you could use DISTINCT, but it would be very inefficient. Better to use EXISTS.


	FROM	jobs, details, seekers 
	WHERE	(
			    details.discipline2=jobs.position 
			AND details.userid=@userid
		) OR
		(
			    seekers.position=jobs.position 
			AND seekers.userid=@userid
		)

For a given Job, if there are both "details" AND "seekers" who match "@userid" then you will again get a cartesian join with everything matched with everything (for the "details" and "seekers" rows matching each "@userid"). See above as you are only selecting columns from "jobs"

Similar situation with your third SELECT

You have passed the three @Position parameters as varchar(150) but only check whether they are empty strings, or not. BIT might be a better datatype to use.

If you first test:
If (@Position1 <> '')
is FALSE then none of the other tests will be TRUE, so your SELECTs 2 & 3 will never be executed.

"id" is a reserve word so it would be safer to reference it using this style:

jobs.[id]

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources