sql-server - SQL Server 存储过程需要未提供的参数

Question

CREATE TABLE [dbo].[review]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [uID] [varchar](6) NOT NULL,
    [pID] [int] NOT NULL,
    [email] [nvarchar](255) NOT NULL,
    [review] [nvarchar](3000) NULL,
    [refURL] [nvarchar](2083) NOT NULL,
    [refID] [nvarchar](100) NOT NULL,
    [cDate] [datetime] NOT NULL,

    CONSTRAINT [PK_review] 
        PRIMARY KEY CLUSTERED ([id] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[review] 
    ADD CONSTRAINT [DF_review_uID] DEFAULT (LEFT(NEWID(), (6))) FOR [uID]
GO

ALTER TABLE [dbo].[review] 
    ADD CONSTRAINT [DF_review_cDate] DEFAULT (GETDATE()) FOR [cDate]
GO

我写了这个存储过程:

ALTER PROCEDURE [dbo].[spReview] 
    @id INT = 0,
    @uID VARCHAR(6),
    @pID INT = 0,
    @email NVARCHAR(255),
    @review NVARCHAR(3000),
    @refURL NVARCHAR(2083),
    @refID NVARCHAR(100),
    @cDate DATETME = NULL,
    @OPERATION NVARCHAR(50) = ''
AS
    IF @OPERATION = 'Insert'
    BEGIN
        DECLARE @inserted TABLE ([uID] VARCHAR(6));

        INSERT INTO review ([pID], [email], [review], [refURL], [refID])
        OUTPUT INSERTED.[uID] INTO @inserted
        VALUES (@pID, @email, @review, @refURL, @refID)

        SELECT *
        FROM @inserted
    END
    ELSE IF @OPERATION = 'Delete'
    BEGIN
        DELETE FROM review
        WHERE id = @id
    END
    ELSE IF @OPERATION = 'Update'
    BEGIN
        UPDATE review
        SET pID = @pID,
            email = @email,
            review = @review,
            refURL = @refURL,
            refID = @refID
        WHERE id = @id
  END

uID : left(newid(),(6)) 和 cDate : getdate() 设置默认值

DECLARE @return_value int

EXEC    @return_value = [dbo].[spReview]
        @id = N'29',
        @OPERATION = N'Delete'

SELECT  'Return Value' = @return_value

GO

执行删除查询时出现此错误:

Procedure or function 'spReview' expects parameter '@uID', which was not supplied

我试过调试，我不知道我哪里出错了。我哪里做错了？

---

ELSE IF @OPERATION = 'Delete'
    BEGIN
        DELETE FROM review
        WHERE id = @id
END

只是等待'@id'参数，不需要'@uID'

Answer 1

这是您的代码:

ALTER PROCEDURE [dbo].[spReview] 
    @id INT = 0,
    @uID VARCHAR(6),
    @pID INT = 0,
    @email NVARCHAR(255),
    @review NVARCHAR(3000),
    @refURL NVARCHAR(2083),
    @refID NVARCHAR(100),
    @cDate DATETME = NULL,
    @OPERATION NVARCHAR(50) = '' 

对于每个没有“=”号的参数，您需要提供一个值。在您的情况下，这意味着您至少需要提供以下参数:

    @uID VARCHAR(6),
    @email NVARCHAR(255),
    @review NVARCHAR(3000),
    @refURL NVARCHAR(2083),
    @refID NVARCHAR(100),

因此，您要么在调用过程时提供这些参数，要么重写您的过程，这样您就不需要这些参数了。