SQL Server 2008 R2 插入带有标识字段的存储过程语法

发布于 2024-11-02 00:09:47 字数 778 浏览 4 评论 0原文

下午好，

我编写了一个非常基本的存储过程，它将用于将 QuestionText 和 QuestionStatus 插入 Questions 表中，并将 QuestionID int 主键设置为具有 Identity。

语法如下：

CREATE PROCEDURE InsertNewQuestion 
-- Add the parameters for the stored procedure here
@QuestionText varchar(200), @QuestionStatus bit
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT into Questions
(QuestionText,QuestionStatus) 
Values
(@QuestionText),(@QuestionStatus)
END
GO

当我执行存储过程时，收到以下错误：

INSERT 语句中的列数多于 VALUES 中指定的值条款。 VALUES 子句中的值数必须与列数匹配在 INSERT 语句中指定。

用于正确插入记录并允许 QuestionID 在每次插入时自动递增的正确语法是什么？

非常感谢您的帮助和指导。

更新了存储过程语法

原文

Good Afternoon,

I've written a very basic stored procedure that will be used to insert QuestionText and QuestionStatus into the Questions table with QuestionID int Primary Key set as having Identity.

The syntax is as follows:

CREATE PROCEDURE InsertNewQuestion 
-- Add the parameters for the stored procedure here
@QuestionText varchar(200), @QuestionStatus bit
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT into Questions
(QuestionText,QuestionStatus) 
Values
(@QuestionText),(@QuestionStatus)
END
GO

When I execute the stored procedure, I receive the following error:

There are more columns in the INSERT statement than values specified in the VALUES
clause. The number of values in the VALUES clause must match the number of columns
specified in the INSERT statement.

What is the correct syntax to use to properly insert a record and allow for QuestionID to auto-increment upon each insertion?

Thanks much for your help and guidance.

Updated Stored Procedure Syntax

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

初见终念 2024-11-09 00:09:47

删除 QuestionID 和 @@Identity + 值应该是 (@QuestionText,@QuestionStatus)

INSERT into Questions
(QuestionText,QuestionStatus) 
Values
(@QuestionText,@QuestionStatus)

SQL Server 知道 QuestionID 是一个 Identity 字段并将递增它....:-)

如果您需要返回插入的 Identity，则创建一个SP 上的 out 参数并通过调用 SCOPE_IDENTITY() 获取它。

设置@QuestionID = SCOPE_IDENTITY()

Remove QuestionID and @@Identity + Values should be (@QuestionText,@QuestionStatus)

INSERT into Questions
(QuestionText,QuestionStatus) 
Values
(@QuestionText,@QuestionStatus)

SQL Server knows that QuestionID is an Identity field and will increment it.... :-)

If you need to return the Identity that was inserted then create an out parameter on the SP and get it by calling SCOPE_IDENTITY().

SET @QuestionID = SCOPE_IDENTITY()

回复收藏 0 原文

我们只是彼此的过ke 2024-11-09 00:09:47

您不指定标识列，并且值不应位于自己的括号中。

INSERT into Questions
(QuestionText,QuestionStatus) 
Values
(@QuestionText, @QuestionStatus)

You do not specify the identity columns, and the values are not supposed to be in their own parenthesis.

INSERT into Questions
(QuestionText,QuestionStatus) 
Values
(@QuestionText, @QuestionStatus)

回复收藏 0 原文

日裸衫吸 2024-11-09 00:09:47

INSERT into Questions (QuestionText,QuestionStatus)  
Values (@QuestionText,@QuestionStatus)

即没有指定身份字段。

INSERT into Questions (QuestionText,QuestionStatus)  
Values (@QuestionText,@QuestionStatus)

i.e. without the identity field specified.

回复收藏 0 原文

反差帅 2024-11-09 00:09:47

CREATE PROCEDURE SPWITHPARAMETER_AND_RETURN_VALUE
  (
    @EMPID INT,
    @IDVAL INT,   
    @RETURNVALUE INT =0 OUT
  )
  AS 
    DECLARE @COUNT INT
  BEGIN
  SELECT @COUNT=COUNT(*) FROM JOINTABLE WHERE EMPID=@EMPID AND IDVAL=@IDVAL
    IF(@COUNT >0)
      BEGIN

           SET @RETURNVALUE = 1;
                             PRINT @RETURNVALUE    
            RETURN @RETURNVALUE
              END
         ELSE
       BEGIN
            SET @RETURNVALUE = 1;
                              PRINT @RETURNVALUE
            RETURN @RETURNVALUE
                        END
  END

CREATE PROCEDURE SPWITHPARAMETER_AND_RETURN_VALUE
  (
    @EMPID INT,
    @IDVAL INT,   
    @RETURNVALUE INT =0 OUT
  )
  AS 
    DECLARE @COUNT INT
  BEGIN
  SELECT @COUNT=COUNT(*) FROM JOINTABLE WHERE EMPID=@EMPID AND IDVAL=@IDVAL
    IF(@COUNT >0)
      BEGIN

           SET @RETURNVALUE = 1;
                             PRINT @RETURNVALUE    
            RETURN @RETURNVALUE
              END
         ELSE
       BEGIN
            SET @RETURNVALUE = 1;
                              PRINT @RETURNVALUE
            RETURN @RETURNVALUE
                        END
  END

回复收藏 0 原文

~没有更多了~