发现创建存储过程时出错 [英] Error found creating stored procedure

问题描述

创建存储过程时发现下面提到的错误



代码是....

Hi,

Below mentioned error found while creating Stored procedure

The Code is....

CREATE PROCEDURE Allocate_call
    @comp_no as numeric(10) = select Complaint_no from Call_Reg,
    @Tech_name as varchar(50) = select Fname from New_Tech,
AS

    insert into Call_allocation values('@comp_no', '@Tech_name')

&错误是



消息156，级别15，状态1，过程Allocate_call，第7行

关键字附近的语法不正确''选择''。

消息1087，等级15，状态2，过程Allocate_call，第8行

必须声明表变量@Tech_name。

消息156，级别15，状态1，过程Allocate_call，第9行

关键字AS附近的语法不正确。







请帮助..

& the error is

Msg 156, Level 15, State 1, Procedure Allocate_call, Line 7
Incorrect syntax near the keyword ''select''.
Msg 1087, Level 15, State 2, Procedure Allocate_call, Line 8
Must declare the table variable "@Tech_name".
Msg 156, Level 15, State 1, Procedure Allocate_call, Line 9
Incorrect syntax near the keyword ''AS''.



Pls help..

推荐答案

CREATE PROCEDURE Allocate_call
AS
BEGIN
   Declare @comp_no as numeric(10)
   Declare  @Tech_name as varchar(50)

   select @comp_no      =   Complaint_no from Call_Reg
   select @Tech_name    =   Fname from New_Tech

    insert into Call_allocation values(@comp_no, @Tech_name)
END

- 删除来自声明的第二个变量的逗号。

- 按照下面的定义放置大括号。

- 可能有多个记录返回的情况。

- A. lso，检查需要准确的查询，因为select将返回n个记录。使用where条件来获得准确的值。

- PS：如果只需要db表中的变量。然后将其声明为局部变量而不是过程参数。



所以使用它并运行并知道是否有任何错误。

- Remove comma from the second variable declared.
- Put braces as defined below.
- There might be case where more than one records are returning.
- Also, check what query needs to be exactly as both select will return n number of records. Use where condition to get exact value.
- PS: If you need the variables from db tables only. Then declare it as local variables instead of procedure parameters.

So use this and run and leme know if there is any error.

CREATE PROCEDURE Allocate_call
    @comp_no as numeric(10) = (select Top 1 Complaint_no from Call_Reg),
    @Tech_name as varchar(50) = (select Top 1 Fname from New_Tech)
AS

    insert into Call_allocation values('@comp_no', '@Tech_name')

试试这个：

Try this:

CREATE PROCEDURE Allocate_call
    @comp_no numeric(10) = NULL,
    @Tech_name varchar(50) = NULL
AS
BEGIN
    --Check for NULL
    SELECT @comp_no = CASE WHEN @comp_no IS NULL THEN (select Complaint_no from Call_Reg) ELSE @comp_no END
    --Check for NULL
    SELECT @Tech_name = CASE WHEN @Tech_name IS NULL THEN (select Fname from New_Tech) ELSE @Tech_name END
    --Insert values
    insert into Call_allocation values(@comp_no, @Tech_name)
END

--Amit

--Amit

这篇关于发现创建存储过程时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！