asked on

How would you modify a Stored Procedure from executing if a table does not exist ?

I am developing an Access ADP application using Access as the front end and SQL Server as the back end database. I execute a Stored Procedure in the Attach Code Snippet that has 3 parts.

Is there a way to execute Part A and if the table in Part A doesn't exist, exit the Stored Procedure ?
If the table in Part A exists, then execute Parts B and C.

Part A)
SELECT       *
FROM         ztblRefresh
 
Part B) 
DELETE FROM ztblCustomers
 
Part C)
INSERT INTO ztblCustomers
SELECT      ztblCustomersNew.*
FROM        ztblCustomersNew

Open in new window

SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dwe761

What does Part A actually do? Does it create ztblRefresh? Otherwise, it sounds like if ztblRefresh exists, you want to execute Parts B and C. Is that correct?
If so, it'd look something like this:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ztblRefresh]') AND type in (N'U'))
BEGIN
 
   Part B) 
   DELETE FROM ztblCustomers
 
   Part C)
   INSERT INTO ztblCustomers
   SELECT      ztblCustomersNew.*
   FROM        ztblCustomersNew
 
 
END