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

I'm guessing the issue is that you didn't define that parameter when calling it. Please include that code as well. — juharr
– juharr, Commented May 29, 2018 at 14:16
The code is working properly. use EXEC Example6 'a','b','c' — Hossein Golshani
– Hossein Golshani, Commented May 29, 2018 at 14:23

Ian-Fogelman · Accepted Answer · 2018-05-29 15:13:17Z

2

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

edited May 29, 2018 at 15:13

Ian-Fogelman

1,6151 gold badge11 silver badges17 bronze badges

answered May 29, 2018 at 14:21

Yan

4333 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HardCode Over a year ago

Absolutely not necessary.

Ian-Fogelman · Accepted Answer · 2018-05-29 15:28:32Z

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

edited May 29, 2018 at 15:28

answered May 29, 2018 at 14:22

Ian-Fogelman

1,6151 gold badge11 silver badges17 bronze badges

2 Comments

S.Vojnovic Over a year ago

I tried that already, but it doesn't give me what I need. I need to list all First names and Last Names from employee table that contains 'son' somewhere in the name.

Ian-Fogelman Over a year ago

If you want to use the parameters passed to your stored proc, I think you will want the last line, however the like '%son%' will return people with 'son' in their names

HardCode · Accepted Answer · 2018-05-29 15:36:13Z

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

Collectives™ on Stack Overflow

Stored Procedure in SQL Server (declaration of variable)

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related