Trying to wrap up a stored procedure

Question

I am new to SQL Server and need help completing a stored procedure. Here is the code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('spCopyPurchaseOrders') IS NOT NULL
    DROP PROC spCopyPurchaseOrders;

GO

CREATE PROC spCopyPurchaseOrders
AS

    IF OBJECT_ID('PurchaseOrders') IS NOT NULL
        DROP TABLE PurchaseOrders;
    SELECT *
    INTO PurchaseOrdersCopy
    FROM PurchaseOrders;

I can't get the sp to recognize the database even though I have a valid USE statement at the top of the script. Any ideas?

It should fail since you've dropped the table `PurchaseOrders`. — Felix Pamittan, Jun 30 '15 at 01:51
It would make more sense if you dropped `PurchaseOrdersCopy` instead of `PurchaseOrders` — Nick.Mc, Jun 30 '15 at 01:53
Also, while you are learning, avoid the `DROP PROC sp` as it clears security settings, impacting db and application. See [this](http://stackoverflow.com/questions/937908/how-to-detect-if-a-stored-procedure-already-exists) and [this](http://stackoverflow.com/questions/2072086/how-to-check-if-a-stored-procedure-exists-before-creating-it) SO posts. Run a trivial SP in `IF` condition, then use `ALTER PROC sp` which retains all security settings, extended properties, dependencies, and constraints. — Parfait, Jun 30 '15 at 02:10
Also, avoid the sp prefix. Old TSQL system stored procedures all started `sp_whatever` and therefore almost any other prefix is preferable. — Stan, Jun 30 '15 at 13:08

score 0 · Answer 1 · answered Jun 30 '15 at 03:15

As coded, the PurchaseOrders table is dropped before the SELECT

IF OBJECT_ID('PurchaseOrders') IS NOT NULL
    DROP TABLE PurchaseOrders;   -- <<< this breaks the SELECT FROM
SELECT *
INTO PurchaseOrdersCopy   
FROM PurchaseOrders;   -- <<< can't possible exist, the IF...DROP saw to it

And since you are using SELECT...INTO, you probably mean PurchaseOrdersCopy

But, this would be much better

 -- create an empty table, but just once, otherwise empty the existing table
IF OBJECT_ID('dbo.PurchaseOrdersCopy') IS NULL
   SELECT *
    INTO dbo.PurchaseOrdersCopy   
    FROM dbo.PurchaseOrders 
    WHERE 1=0 
ELSE IF EXISTS (SELECT * FROM dbo.PurchaseOrdersCopy  )
   DELETE FROM dbo.PurchaseOrdersCopy; -- HINT: see if TRUNCATE TABLE dbo.PurchaseOrdersCopy is an option for you

-- this will be longer if you have an IDENTITY column
-- SET IDENTITY_INSERT ON (for example)
INSERT INTO dbo.PurchaseOrdersCopy   
SELECT *
FROM dbo.PurchaseOrders;

Specifically:

a) try and avoid DROP and CREATE when you can

b) prefix your object names with schema name (dbo. by default)

Concerns: how will this work if the procedure can be run more than once or by more than one person (dbo.PurchaseOrdersCopy is shared)

Trying to wrap up a stored procedure

1 Answers1