必须在sql过程中声明标量变量

Please what is wrong with the procedure statement below

请问下面的程序声明有什么问题

    DECLARE  @result int 
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    set @result = (select COUNT(*) from populate)

    if (@result > 1)
    Begin
        insert into populate (brch, terminal_id) values(@branch, @atmid)
    end

    SET ANSI_NULLS ON
    GO
     SET QUOTED_IDENTIFIER ON
    GO

    CREATE PROCEDURE insertion @id varchar(50), @brch varchar(50)
    -- Add the parameters for the stored procedure here

   AS
    BEGIN
   DECLARE  @result int 
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    set @result  = (COUNT(*) from populate)

    if (@result > 1)
    Begin
        insert into populate (brch, terminal_id) values(@id, @brch)
    end
    END
    GO

3 个解决方案

#1

It seems that you have confused things by first posting a piece of code that gives the error Msg 137, Level 15, State 2, Line 11 Must declare the scalar variable "@branch". and then later adding a complete procedure that gives the error Msg 156, Level 15, State 1, Procedure insertion, Line 13 Incorrect syntax near the keyword 'from'.

看起来你混淆了一些东西，首先发布了一段代码，它给出了错误消息137，级别15，状态2，行11必须声明标量变量“@branch”。然后添加一个完整的过程，在关键字'from'附近给出错误消息156，级别15，状态1，过程插入，第13行不正确的语法。

Please make sure that you post the real code you're using and the full error message too, otherwise people cannot help you.

请确保您发布正在使用的真实代码和完整的错误消息，否则人们无法帮助您。

Anyway, I ignored the code snippet and looked only at the procedure and as ABFORCE said, the problem is where you populate @result because your syntax is wrong. This procedure code parses without error in SQL Server 2008:

无论如何，我忽略了代码片段，只查看了程序，正如ABFORCE所说，问题在于你填充@result，因为你的语法错误。此过程代码在SQL Server 2008中无错误地解析：

   CREATE PROCEDURE insertion @id varchar(50), @brch varchar(50)
    -- Add the parameters for the stored procedure here

   AS
    BEGIN
   DECLARE  @result int 
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    select @result  = COUNT(*) from populate

    if (@result > 1)
    Begin
        insert into populate (brch, terminal_id) values(@id, @brch)
    end
    END
    GO

You might want to review the documentation for assigning values to variables and the SET keyword.

您可能希望查看用于为变量和SET关键字赋值的文档。

#2

The problem, as it is said in the error message is that @branch and @atmid are not mentioned anywhere before their values are used.

正如在错误消息中所说的那样，问题是@branch和@atmid在使用它们的值之前没有被提及。

You have declared @result and set it's value, so you need to do the same for @branch and @atmid as well, the system cannot divine the values for you.

你已经声明了@result并设置了它的值，所以你需要为@branch和@atmid做同样的事情，系统不能为你赋值。

#3

Try this

尝试这个

select @result =COUNT(*) from populate

#1