Syntax error with BEGIN

Question

Syntax error with BEGIN

cdun2

SSCertifiable

Points: 7893
More actions
August 21, 2003 at 3:51 pm

#101637

Hello,
I am in the process of building a query that will contain a couple of BEGIN/END blocks seperated by ELSE, and I am having trouble with the first BEGIN/END block.
The following is the first block of code:
***********************************************************
IF
(SELECT SystemUserName FROM Teacher_Data_Main
WHERE (SystemUserName = SYSTEM_USER)
or SystemUserName <> 'valverde\pGist'
or SystemUserName <> 'valverde\jGolden'
or SystemUserName <> 'valverde\tPeepgrass'
or SystemUserName <> 'valverde\lGallo'
or SystemUserName <> 'valverde\cBerry'
or SystemUserName <> 'valverde\aGuild'
or SystemUserName <> 'valverde\rGlenn'
or SystemUserName <> 'valverde\mSarkissian'
or SystemUserName <> 'valverde\cJenkins'
or SystemUserName <> 'valverde\rKepler')
BEGIN
SELECT * FROM tblTests
END
********************************************************
I am getting a syntax error at 'BEGIN'. I've tried to follow this per the examples in BOL. What do I need to correct?
Thanks!
CSDunn

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

mccork SSC-Insane Points: 22288 More actions · Answer 1

The syntax problem is that your subselect, ie:

 

(SELECT SystemUserName ....

.... <> 'valverde\rKepler')

is expected to return a single value, which then must be compared with something.

So, prior to the BEGIN you need a comparison operator and value. Eg.

 

IF

(SELECT SystemUserName FROM Teacher_Data_Main

WHERE (SystemUserName = SYSTEM_USER)

or SystemUserName <> 'valverde\pGist'

or SystemUserName <> 'valverde\jGolden'

or SystemUserName <> 'valverde\tPeepgrass'

or SystemUserName <> 'valverde\lGallo'

or SystemUserName <> 'valverde\cBerry'

or SystemUserName <> 'valverde\aGuild'

or SystemUserName <> 'valverde\rGlenn'

or SystemUserName <> 'valverde\mSarkissian'

or SystemUserName <> 'valverde\cJenkins'

or SystemUserName <> 'valverde\rKepler')

 = 'somevalue'



BEGIN



SELECT * FROM tblTests

END

However, looking at the code, I fear there's other logic problems to be overcome.

OR's don't go well with "NOT Equal" operators.

Also, your subquery will have to be tighted to ensure it returns a maximum of 1 row.

Cheers,

- Mark

Cheers,
- Mark

SQLBill SSC Guru Points: 51440 More actions · Answer 2

Looking at your code, it looks like you DON'T want any SYSUSERNAME from this list:

'valverde\pGist'

'valverde\jGolden'

'valverde\tPeepgrass'

'valverde\lGallo'

'valverde\cBerry'

'valverde\aGuild'

'valverde\rGlenn'

'valverde\mSarkissian'

'valverde\cJenkins'

'valverde\rKepler'

Is that correct, that you don't want ANY of those names?

If so, you need to change the ORs to ANDs.

-SQLBill

cdun2 SSCertifiable Points: 7893 More actions · Answer 3

I want the application to work so that when a user logs on with their domain account ('valverde\(user name)'), the application will look for a match of the SYSTEM_USER value and the value in the 'SystemUserName' field from a table called Teacher_Data_Main. If there is a match between the logged on SYSTEM_USER, or the SystemUserName is NOT one of the names in the list, then BEGIN a certain query, ELSE if the SystemUserName = one of the names in the list, BEGIN a certain other query.

If I used AND instead of OR, wouldn't I be asking the WHERE condition to evalueate a single record for each domain login in the list?

What would be another way to approach this?

Thanks for your help!

CSDunn

cdun2 SSCertifiable Points: 7893 More actions · Answer 4

quote:
If I used AND instead of OR, wouldn't I be asking the WHERE condition to evalueate a single record for each domain login in the list?

Okay, I got the following SELECT query to return the results that I wanted:

****************************************************

SELECT SystemUserName From Teacher_Data_Main

WHERE

SystemUserName = SYSTEM_USER or

(SystemUserName <> 'valverde\pGist'

and SystemUserName <> 'valverde\jGolden'

and SystemUserName <> 'valverde\tPeepgrass'

and SystemUserName <> 'valverde\lGallo'

and SystemUserName <> 'valverde\cBerry'

and SystemUserName <> 'valverde\aGuild'

and SystemUserName <> 'valverde\rGlenn'

and SystemUserName <> 'valverde\mSarkissian'

and SystemUserName <> 'valverde\cJenkins'

and SystemUserName <> 'valverde\rKepler')

*****************************************************

How might I apply this logic to an IF;BEGIN;END;ELSE situation?

Thanks again!

CSDunn

cdun2 SSCertifiable Points: 7893 More actions · Answer 5

I've applied the following to the SELECT list and it looks like I've got something to work with now:

*********************************************

IF

(SELECT COUNT( SystemUserName)AS ACount FROM Teacher_Data_Main

WHERE

SystemUserName = SYSTEM_USER or

(SystemUserName <> 'valverde\pGist'

and SystemUserName <> 'valverde\jGolden'

and SystemUserName <> 'valverde\tPeepgrass'

and SystemUserName <> 'valverde\lGallo'

and SystemUserName <> 'valverde\cBerry'

and SystemUserName <> 'valverde\aGuild'

and SystemUserName <> 'valverde\rGlenn'

and SystemUserName <> 'valverde\mSarkissian'

and SystemUserName <> 'valverde\cJenkins'

and SystemUserName <> 'valverde\rKepler')

and SystemUserName IS NOT NULL)

<

(SELECT COUNT(SystemUserName)AS BCount FROM Teacher_Data_Main

WHERE SystemUserName IS NOT NULL)

BEGIN

PRINT 'The condition is true'

END

ELSE

PRINT 'The condition is not true'

****************************************************

I'll see how far I can get with this idea. Thanks!

CSDunn