if exists/if not exists problem 
Author Message
 if exists/if not exists problem

Hello,

Does anyone know how to get this hypothetical piece of SQL to work:

1> if exists (select * from sysobjects where name = 'fred')
2>     select * from fred
3> go
Msg 208, Level 16, State 1:
Server 'DEAP_ABDEV', Line 2:
Invalid object name 'fred'.

As you can see, the SQL parser flags an error on the non-existent object 'fred'. More or less rules out if exists checking for us on non-existent objects.

Our version of SQL Server is:

SQL Server/4.9.1/EBF 1440 Rollup/Sun4/OS 4.1.2/1/OPT/Wed Mar 17 20:25:05 PST 1993

Thanx,

Marcus.



Mon, 01 Jul 1996 19:03:31 GMT
 if exists/if not exists problem

Marcus Ridgway writes
Quote:
> Hello,

> Does anyone know how to get this hypothetical piece of SQL to work:

> 1> if exists (select * from sysobjects where name = 'fred')
> 2>     select * from fred
> 3> go
> Msg 208, Level 16, State 1:
> Server 'DEAP_ABDEV', Line 2:
> Invalid object name 'fred'.

> As you can see, the SQL parser flags an error on the non-existent object  

'fred'. More or less rules out if exists checking for us on non-existent  
objects.

Quote:

> Our version of SQL Server is:

> SQL Server/4.9.1/EBF 1440 Rollup/Sun4/OS 4.1.2/1/OPT/Wed Mar 17 20:25:05  
PST 1993

> Thanx,

> Marcus.

Try:
if (exists (select * from sysobjects where name = 'fred'))
begin  
        select * from fred
end
go

BS



Tue, 02 Jul 1996 23:42:09 GMT
 if exists/if not exists problem
Fred has to exist for this to compile, so.... no, you can't
(meaning, I've tried to trick it and have never gotten it to
work). However, there are ways to play with the names in
sysobjects, but this is a bit on the {*filter*} side (i.e. "if not
exists fred, update name = "fred" where name = "dummy").

Now, if you're in an external program (e.g. unix shell script
or db-lib program), you can use two querries.

Topher
---
Christopher A. White          | "I once could see,
McCaw Cellular Communications |      but now, at last, I'm blind."
Kirkland, Wa                  |         -- Dream Theatre



Tue, 02 Jul 1996 17:30:44 GMT
 if exists/if not exists problem

Quote:
>Hello,

>Does anyone know how to get this hypothetical piece of SQL to work:

>1> if exists (select * from sysobjects where name = 'fred')
>2>     select * from fred
>3> go
>Msg 208, Level 16, State 1:
>Server 'DEAP_ABDEV', Line 2:
>Invalid object name 'fred'.

>As you can see, the SQL parser flags an error on the non-existent object 'fred'. More or less rules out if exists checking for us on non-existent objects.

>Our version of SQL Server is:

>SQL Server/4.9.1/EBF 1440 Rollup/Sun4/OS 4.1.2/1/OPT/Wed Mar 17 20:25:05 PST 1993

>Thanx,

>Marcus.

Maybe you have to fully qulify the object.  Do a SELECT on 'fred' from
sysobjects, this will give a list of all 'fred' objects and their owners.
Then, in your SELECT statement prefix 'fred' with proper owner.
EXAMPLE:
        select * from dbo.fred

            OR MAYBE

        select * from user1.fred

Good luck

Yevgeny (Gene) Dubensky



Wed, 03 Jul 1996 00:34:22 GMT
 if exists/if not exists problem

Quote:

>>Hello,

>>Does anyone know how to get this hypothetical piece of SQL to work:

>>1> if exists (select * from sysobjects where name = 'fred')
>>2>     select * from fred
>>3> go
>>Msg 208, Level 16, State 1:
>>Server 'DEAP_ABDEV', Line 2:
>>Invalid object name 'fred'.

>>As you can see, the SQL parser flags an error on the non-existent object 'fred'. More or less rules out if exists checking for us on non-existent objects.

>>Our version of SQL Server is:

>>SQL Server/4.9.1/EBF 1440 Rollup/Sun4/OS 4.1.2/1/OPT/Wed Mar 17 20:25:05 PST 1993

>>Thanx,

>>Marcus.

>Maybe you have to fully qulify the object.  Do a SELECT on 'fred' from
>sysobjects, this will give a list of all 'fred' objects and their owners.
>Then, in your SELECT statement prefix 'fred' with proper owner.
>EXAMPLE:
>    select * from dbo.fred

>        OR MAYBE

>    select * from user1.fred

>Good luck

>Yevgeny (Gene) Dubensky

Qualifying objects has nothing to do with the issue.

First you have to realise that the parser is like a preprocessor that
validates the syntax of the SQL statement as well as the existance of objects
in the statement (without going into detail there are some exceptions if a batch
contains a create statement).

if exists on the other hand is dynamically evaluated when the statements is
submitted for execution.

Of course, in the case of using if exists in the way the original poster described
the parser fails before there is a chance to evaluate the statement.

In order to make this piece of SQL to work (assuming it is contained in a stored
procedure) is to define the table temporarily while creating the sproc.

After the parser did it's job, you can drop  the table and execute the sproc
and see how it works.
e.g.:
create table foo (bar int)
go
create proc foobar as
if exists (select * from sysobjects where name = 'foo')
        select * from foo
go
exec foobar
go
drop table foo
go
exec foobar
go

Kobi

---

                                 +++++++++++++++++++++++++++++++++

               Kobi Lifshitz     + Office:       (301) 564-5322  +
                                 + FAX:          (301) 564-0885  +
                                 +++++++++++++++++++++++++++++++++
                    OR

                                 +++++++++++++++++++++++++++++++++

               Kobi Lifshitz     + Office:       (202) 752-8396  +
                                 + FAX:          (202) 752-5460  +
                                 +++++++++++++++++++++++++++++++++



Wed, 03 Jul 1996 04:52:54 GMT
 if exists/if not exists problem

Quote:


>Qualifying objects has nothing to do with the issue.
>First you have to realise that the parser is like a preprocessor that
>validates the syntax of the SQL statement as well as the existance of objects
>in the statement (without going into detail there are some exceptions if a
>batch contains a create statement).
>if exists on the other hand is dynamically evaluated when the statements is
>submitted for execution.

Good comment.  Some additonal comment (please correct me if wrong):

The body of IF block happened to be
        select * from fred
which requires the parser/compiler obtain the object id of the table fred
so that a query plan can be generated (and optimized.)
Therefore the original author's IF block will not compile if the table
doesn't exist.

On the other hand, if the statement were
        if exists (select * from sysobjects where name = 'fred')
                drop table fred         /* or: exec sp_help fred */
                ^^^^                            ^^^^^^^^^^^
        go

then the whole thing will compile even if 'fred' doesn't exist.
The reason (I think) is that 'fred' is treated like a string parameter
to 'drop'.  Thus the compiler only needs to establish the function call
interface to 'drop' without knowing what fred is.
It's kind of an odd thing since both SELECT and DROP
are standard SQL statements and people would assume that if SELECT doesn't
work, DROP won't either.  However, DROP doesn't need to be optimized and
there's no "plan" for it in the sense that DROP could be implemented as
a straight, hard-coded procedure that remains the same forever.
Every time we issue DROP, it always call the same executable with different
parameters.  On the other hand, every SELECT has it's own plan generated
each time and the table name is no longer treated as a parameter, but a
concrete object.
that must exist.

--

Vincent Q. Yin



Wed, 03 Jul 1996 14:01:55 GMT
 if exists/if not exists problem

Quote:

>Marcus Ridgway writes
>> Does anyone know how to get this hypothetical piece of SQL to work:

>> 1> if exists (select * from sysobjects where name = 'fred')
>> 2>     select * from fred
>> 3> go
>> Msg 208, Level 16, State 1:
>> Server 'DEAP_ABDEV', Line 2:
>> Invalid object name 'fred'.
>>...

>Try:
>if (exists (select * from sysobjects where name = 'fred'))
>begin
>       select * from fred
>end
>go

Did you try it yourself? It doesn't seem like that, since what you
suggested is perfectly equivalent with Marcus' code.

However, the extract may work as part of a stored procedure, provided
that the table fred exists at the time the stored procedure is loaded.
(But what happens with "select *" if the table has been dropped and
recreated, I don't want to know.)

To be robust the extract should in such case be supplemented with

   AND uid IN (user_id(), 1)

provided that user_id really is the name of the built-in to use here.

As you may guess, I haven't tried any of my suggestions in practice...
--

Not spokesman for ENEA Data AB



Wed, 03 Jul 1996 08:14:22 GMT
 if exists/if not exists problem
|> Hello,
|>
|> Does anyone know how to get this hypothetical piece of SQL to work:
|>
|> 1> if exists (select * from sysobjects where name = 'fred')
|> 2>     select * from fred
|> 3> go
|> Msg 208, Level 16, State 1:
|> Server 'DEAP_ABDEV', Line 2:
|> Invalid object name 'fred'.

Try:
    if exists (select * from sysobjects where name = 'fred' and type = 'U')
        select * from fred
    go

Then you will find 'fred' only if it is a usertable.

---TeHo
----------------------------------------------------------------------
        Terje Hopso                      Current Software A.S

        Gamle Enebakk vei 65
        N-1188 OSLO
----------------------------------------------------------------------



Wed, 03 Jul 1996 19:38:27 GMT
 
 [ 8 post ] 

 Relevant Pages 

1. where EXISTS / NOT EXISTS do not work (I am really pannic)

2. EXISTS/NOT EXISTS vs. IN/NOT IN

3. Exaple of Exists an Not Exists

4. EXISTS/NOT EXISTS

5. Exists and NOT exists

6. problem in server not exist or connection problem

7. need to copy existing Access data into existing SQL tables, maintaining identity values

8. Add Existing Data File To Existing Database?

9. How to check if primary key constraint exist and drop it if it exist

10. File.Exists returns false even file exists

11. Add Existing Data File To Existing Database?

12. need to copy existing Access data into existing SQL tables, maintaining identity values


 
Powered by phpBB® Forum Software