If Exists 1

[Dashley]

I'm having a problem with this if exists statement

IF EXISTS (SELECT     scac
                        FROM         accessorials
                        WHERE     (CustScac = 'CTL1') AND (CustNo = '123')) 

ELSE
    SELECT     SCAC
     FROM         accessorials
     WHERE     (CustScac = 'CTL1')

It returns an error: Incorrect syntax near keyword ELSE.
I can't see what wrong with it. Both cluses work independantly. It SQL Server express in asp.net 2005


thanks

 

[bborissov]

How about:

IF NOT EXISTS (SELECT scac
                      FROM  accessorials
                      WHERE (CustScac = 'CTL1') AND
                            (CustNo = '123'))
    SELECT SCAC
           FROM  accessorials
           WHERE (CustScac = 'CTL1')

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Dashley]

You would think it would but the analyzer says the NOT is not supported aghhhhh

 

[bborissov]

That works for me:

DECLARE @TEst TABLE (scac int, CustScac varchar(20),  CustNo varchar(20))

IF NOT EXISTS (SELECT scac
                  FROM  @TEst accessorials
                  WHERE (CustScac = 'CTL1') AND (CustNo = '123'))
    SELECT     SCAC
     FROM     @TEst   accessorials
     WHERE     (CustScac = 'CTL1')

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Dashley]

I believe it is working on your end. Mine keeps saying it not supported. It must be the express version that I'm using.

 

[bborissov]

Dashley,
Did you tried the example I post at YOUR end?
No matter what version of SQL Server you use this should works. The example you post didn't, just because TSQL didn't like empty IFs and BEGIN END blocks.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Dashley]

Yes, I pasted it in just as you had it. The first thing I got was;

The Declare cursor SQL construct or statement is not supported.

If I try the first staement you sent and the one I made I get :

The NOT SQL construct or statement is not supported.

It should work I agree.

I'm using the query pane in VS 2005. Microsoft SQL Server 09.00.3068.

weird huh

 

[bborissov]

Query pane is not the best tool.
It is try to get an editable resultset, that is why you can't use the full TSQL there. Try to download and use SQL Server Management Studio Express.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Dashley]

Hee hee. I downloaded it, went to install it and found I already have a newer version of it installed locally ha.
And it now works as does your code.

Thanks for hanging in there I really do appreciate it.

 

[ArunPrithviraj]

Hi Dashley,

I'm not sure whether by this time you might have found a solution for the above stated issue.

But in case you have not or you find the below one is a better solution performance wise please go ahead.
Check it out before you proceed only if u have time.

IF EXISTS
(
SELECT 1
FROM accessorials
WHERE (CustScac = 'CTL1') AND (CustNo = '123')
)
BEGIN

SELECT scac
FROM accessorials
WHERE (CustScac = 'CTL1') AND (CustNo = '123'))

END
ELSE
BEGIN

SELECT SCAC
FROM accessorials
WHERE (CustScac = 'CTL1')

END

Thanks,
AP

 

[SQLSister]

And incidentally you do know you should try really really hard to never use a cursor, right? Cursors are extremely bad for system performance. The majority of the time they should not be used for inserts, updates, deletes as there are set-based alternatives that will perform better.

"NOTHING is more important in a database than integrity." ESquared