sql-server - SQL Server 中的存储过程（变量声明）

Question

我正在尝试在 SQL Server 中创建一个存储过程，但每当我尝试执行它时，我都会收到错误消息：

Msg 137, Level 15, State 2, Line 48
必须声明标量变量“@FirstName”

我以为我FirstName在WHERE条件下声明了，但它似乎不正确。

CREATE PROCEDURE Example6
    @FirstName VARCHAR(50),
    @LastName VARCHAR(50),
    @MiddleName VARCHAR(50)
AS 
    SELECT  
        [employee_id], [employee_type_id], 
        [HR_status_id], [employer_id],
        [fName], [mName], [lName],
        [address], [city], [state_id], [zip],
        [hireDate], [currDate],
        [ssn], [ext_emp_id],
        [terminationDate], [dob],
        [initialMeasurmentEnd], 
        [plan_year_id], [limbo_plan_year_id], [meas_plan_year_id],
        [modOn], [modBy],
        [plan_year_avg_hours], [limbo_plan_year_avg_hours],
        [meas_plan_year_avg_hours], [imp_plan_year_avg_hours],
        [classification_id], [aca_status_id], [ResourceId]
    FROM 
        dbo.employee
    WHERE 
       [fName] = @FirstName 
       AND [lName] = @LastName 
       AND [mName] = @MiddleName
       AND @FirstName LIKE '%son%' OR @LastName LIKE '%son%';

标签： sql-serverstored-procedures

Answer 1

您应该在参数子句中添加括号。
试试那个：

CREATE PROCEDURE Example6

(@FirstName varchar(50),
@LastName varchar(50),
@MiddleName varchar(50))
AS 
SELECT [employee_id]
      ,[employee_type_id]
      ,[HR_status_id]
      ,[employer_id]
      ,[fName]
      ,[mName]
      ,[lName]
      ,[address]
      ,[city]
      ,[state_id]
      ,[zip]
      ,[hireDate]
      ,[currDate]
      ,[ssn]
      ,[ext_emp_id]
      ,[terminationDate]
      ,[dob]
      ,[initialMeasurmentEnd]
      ,[plan_year_id]
      ,[limbo_plan_year_id]
      ,[meas_plan_year_id]
      ,[modOn]
      ,[modBy]
      ,[plan_year_avg_hours]
      ,[limbo_plan_year_avg_hours]
      ,[meas_plan_year_avg_hours]
      ,[imp_plan_year_avg_hours]
      ,[classification_id]
      ,[aca_status_id]
      ,[ResourceId]
     FROM dbo.employee
    WHERE  @FirstName LIKE '%son%' OR @LastName LIKE '%son%';