sql server-试图包装存储过程

Question

我是SQL Server的新手，需要帮助完成存储过程。这是代码：

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;

即使在脚本顶部有一个有效的USE语句，我也无法让sp识别数据库。有什么想法吗？

Answer 1

编码时，PurchaseOrders表在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

既然你使用的是SELECT...INTO，你可能是指PurchaseOrdersCopy

但是，这将是更好的

 -- 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;    

具体而言：

a） 当你可以时，尽量避免DROP和CREATE

b） 在对象名称前面加上模式名称（默认为dbo.）

关注点：如果该过程可以多次运行或由多个人运行（dbo.PurchaseOrdersCopy是共享的），