Stored Procedure in SQL Server (declaration of variable)

Question

I'm trying to create a stored procedure in SQL Server, but I keep getting an error whenever I try to execute it:

Msg 137, Level 15, State 2, Line 48
Must declare the scalar variable "@FirstName"

I thought I declared FirstName in WHERE condition, but it doesn't seem right.

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%';

Answer 1

In SSMS, if you right-click Stored Procedures and select New Stored Procedure, you'll get this template generated for you. Notice the BEGIN and END that are missing from your SP:

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
    -- Add the parameters for the stored procedure here
    <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
    <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

Answer 2

You need to wrap the parameters for the stored procedure in (), then call the stored procedure such as:

EXEC Procedure 'Son','Son','Son'

       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 @FirstName OR [LastName] LIKE @LastName

Answer 3

You should add parentheses in the parameters clause.
Try that one:

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%';