[dba-SQLServer]SQL Server 7 - Stored Procedure Help - Quite Urgent.

Francisco H Tapia my.lists at verizon.net
Tue Feb 25 18:11:09 CST 2003


NOT EXIST isn't exposed to the same side effects... Take the following for
an example...


BEGIN TRANSACTION
SELECT COUNT(*) FROM Titleauthor
DELETE Titleauthor
    WHERE au_id IN ( SELECT au_id FROM Publishers)
SELECT COUNT(*) FROM Titleauthor
ROLLBACK

however this statement dosent error out, true it's the EXISTS not the NOT
EXISTS but the side-effects are the same...

BEGIN TRANSACTION
SELECT COUNT(*) FROM Titleauthor
DELETE Titleauthor
    WHERE EXISTS(SELECT au_id FROM Publishers
      WHERE Publishers.au_id = Titleauthor.au_id)
SELECT COUNT(*) FROM Titleauthor
ROLLBACK

note that you now get the invalid column au_id with the EXISTS
statement...which is at it should be... the first IN statement doesn't give
this feedback, a workaround is to declare the au_id in the IN statement as
Publisher.au_id in order to cause it to explicilty resolve in which case
you'd get the error..

-Francisco
http://rcm.netfirms.com
----- Original Message -----
From: "Djabarov, Robert" <Robert.Djabarov at usaa.com>
To: <dba-sqlserver at databaseadvisors.com>
Sent: Tuesday, February 25, 2003 12:19 PM
Subject: RE: [dba-SQLServer]SQL Server 7 - Stored Procedure Help - Quite
Urgent.


: NOT IN has the same side effects as NOT EXISTS.  It can be easily
re-written as follows:
:
: insert into tableA (Payroll, JobDate, JobType)
:    select B.Payroll, B.JobDate, B.JobType
:       from tableB B left outer join (
:          select A.Payroll, A.JobDate, A.JobType
:             from tableA A
:                inner join tableB tb
:                   on A.Payroll = tb.Payroll
:                   AND A.Jobdate = tb.JobDate
:                   AND A.JobType = tb.JobType
:          ) t  on B.Payroll = t.Payroll
:              and B.JobDate = t.JobDate
:              and B.JobType = t.JobType
:    where  t.Payroll is null
:       and t.JobDate is null
:       and t.JobType is null
:
:
: ______________________________________________________
: Robert Djabarov
: Certified MS SQL Server DBA
: Certified MS SQL Server Programmer
: Certified MS VB Programmer
: ? (210) 913-3148 - phone
: ( (210) 753-3148 - pager
:
:  -----Original Message-----
: From: Francisco H Tapia [mailto:my.lists at verizon.net]
: Sent: Tuesday, February 25, 2003 10:36 AM
: To: dba-sqlserver at databaseadvisors.com; AccessD;
dba-VB at databaseadvisors.com
: Subject: Re: [dba-SQLServer]SQL Server 7 - Stored Procedure Help - Quite
Urgent.
:
: How about ....
:
: Insert into tableA (Payroll, jobdate, jobtype)
:     Select Payroll, jobdate, jobtype
:     From tableB AS B
:     WHERE NOT EXISTS( Select Payroll, jobdate, jobtype
:                                             From tableA AS A
:                                             Where B.Payroll = A.Payroll
AND
: B.Jobdate = A.JobDate AND B.JobType = A.JobType)
:
: I could have used a NOT IN clause but then NOT IN can have some serious
side
: effects...In General the above statement will be evaluated by the engine
: where as a NOT IN could miss evaluating the B alias in the where clause
and
: cause it to report bad data, using Exists (or NOT EXISTS) usually yields
: faster performance and the assurance that it evaluates correctly.
: -Francisco
: http://rcm.netfirms.com
: ----- Original Message -----
: From: <paul.hartland at fsmail.net>
: To: <dba-SQLServer at databaseadvisors.com>; <accessd at databaseadvisors.com>;
: <dba-vb at databaseadvisors.com>
: Sent: Tuesday, February 25, 2003 1:42 AM
: Subject: [dba-SQLServer]SQL Server 7 - Stored Procedure Help - Quite
Urgent.
:
:
: : To all,
: :
: : Watch for Word Wrap...............
: :
: : Being quite a novice with Stored Procedures on SQL Server 7.0, I'm
looking
: for some pretty urgent help at the moment.  I have two tables, lets say
: Table A & Table B.  Table A is my master table, and Table B is a temporary
: table.  Before Table be gets populated with any data, every existing
record
: is deleted so I start with a fresh table so to speak.  Table B consists of
3
: fields PayrollNo, JobDate, JobType (these fields are also in the master
: table, but the master table is used to contain other consting information,
: which is entered at a later date).
: :
: : What I would like is a Stored Procedure which would Insert Table B
: contents into Table A, but making sure that I don't insert any duplicates
: into Table A (something like an unmatched query in access but with an
Insert
: also).  i.e.
: :
: : Table A                                                       Table B
: : payroll  jobdate    jobtype                                   id
payroll
: jobdate    jobtype
: : 999001   01/01/01   0                                          1  999001
: 01/01/01   0
: : 999001   01/01/01   2                                          2  999001
: 01/01/01   1
: : 999002   01/01/01   2                                          3  999001
: 01/01/01   2
: :                                                                4  999002
: 01/01/01   0
: :                                                                5  999002
: 01/01/01   1
: :                                                                6  999002
: 01/01/01   2
: :
: : So when I invoke the stored procedure, it would insert records 2, 4 and
5
: from Table B into Table A........Anyone any idea what I'm on about, if so
: can anybody please help.......
: :
: : Thanks in advance
: :
: : Paul Hartland
: :
: :
__________________________________________________________________________
: : Freeserve AnyTime - Go online whenever you want for just £6.99 a month
for
: : your first 3 months, that's HALF PRICE! And then it's just £13.99 a
month
: : after that.
: :
: : For more information visit http://www.freeserve.com/time/ or call free
on
: : 0800 970 8890
: :
: :
: : _______________________________________________
: : dba-SQLServer mailing list
: : dba-SQLServer at databaseadvisors.com
: : http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
: : http://www.databaseadvisors.com
: :
:
:
: _______________________________________________
: dba-SQLServer mailing list
: dba-SQLServer at databaseadvisors.com
: http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
: http://www.databaseadvisors.com
:
:
:
: _______________________________________________
: dba-SQLServer mailing list
: dba-SQLServer at databaseadvisors.com
: http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
: http://www.databaseadvisors.com
:





More information about the dba-SQLServer mailing list