Must declare the scalar variable? Microsoft SQL Server

Question

This code is throwing an error:

Must declare scalar variable @MANDATORY"

Code:

CREATE PROCEDURE [dbo].[CRE_STATION_PR]
    @P_CODE VARCHAR(10),
    @P_NAME VARCHAR(50),
    @P_COORDINATE_X FLOAT,
    @P_COORDINATE_Y FLOAT,
    @P_LOCATION VARCHAR(50),
    @P_STATE_PURPOSE VARCHAR(200),
    @P_MANDATORY BIT,
    @P_ID_STATE INT
AS
    INSERT INTO TBL_STATIONS (CODE, NAME, COORDINATE_X, COORDINATE_Y, LOCATION, STATE_PURPOSE, MANDATORY, ID_STATE) 
    VALUES (@P_CODE, @P_NAME, @P_COORDINATE_X, @P_COORDINATE_Y, @P_LOCATION, @P_STATE_PURPOSE, @MANDATORY, @P_ID_STATE)

    RETURN 0
GO

What should I do?

Fix the typo in your statement, where you use `@MANDATORY` instead of the parameter you declared as `@P_MANDATORY`. And learn to read the words in the error message, which tells you **exactly** what the problem is, and two seconds reading the code looking for use of `MANDATORY` tells you exactly where to find the problem. — Ken White, Jul 03 '16 at 05:19
I know, dumb mistake. That's what happens after doing more than 200 procedures in a day haha. — Felipe Calderon, Jul 03 '16 at 05:22
@FelipeCalderon if you have a reason for that number of such primitive SPs - use code generation. You can collect everything for this particular SP by very simple dynamic sql query. — Ivan Starostin, Jul 03 '16 at 07:21
I will definitely look into dynamic sql queries later, thank you — Felipe Calderon, Jul 03 '16 at 09:45

score 2 · Accepted Answer · answered Jul 03 '16 at 05:12

There is a typo in your insert query

INSERT INTO TBL_STATIONS 
(CODE,NAME,COORDINATE_X,COORDINATE_Y,LOCATION,STATE_PURPOSE,MANDATORY,ID_STATE) 
VALUES 
(@P_CODE,@P_NAME,@P_COORDINATE_X,@P_COORDINATE_Y,@P_LOCATION,@P_STATE_PURPOSE,
@P_MANDATORY, --Here P_ missing 
@P_ID_STATE)

Must declare the scalar variable? Microsoft SQL Server

1 Answers1