Must declare the scalar variable Error while executing stored procedure

Question

I want to insert a new record in a table using stored procedure:

string cs = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;

using (SqlConnection con = new SqlConnection(cs))
{
    string name = txtEmployeeName.Text;
    string gender = ddlGender.SelectedValue;
    int salary = Convert.ToInt32(txtSalary.Text);

    SqlParameter op = new SqlParameter();
    op.ParameterName = "@EmployeeId";
    op.SqlDbType = System.Data.SqlDbType.Int;
    op.Direction = System.Data.ParameterDirection.Output;

    string str = string.Format( "exec spAddEmployee '{0}', '{1}', '{2}', {3}", name,gender,salary,op);

    SqlCommand cmd = new SqlCommand(str,con);
    con.Open();
    cmd.ExecuteNonQuery();

    string EmpId = op.Value.ToString();

    lblMessage.Text = "Employee ID = " + EmpId;  
}

I am not using cmd.Parameters.AddWithValue(), instead I am passing parameters as shown above. But I am getting an error:

Must declare the scalar variable "@EmployeeId".

Here is my stored procedure:

CREATE PROCEDURE spAddEmployee  
    @Name NVARCHAR(50),  
    @Gender NVARCHAR(20),  
    @Salary INT, 
    @EmployeeId INT OUT
AS 
BEGIN
    INSERT INTO tblEmployees 
    VALUES (@Name, @Gender, @Salary)  

    SELECT @EmployeeId = SCOPE_IDENTITY()  
END

That's how the parameters not passed to the Stored procedure from C#. You need to add parameter in command with proper values and direction. — Chetan, Apr 07 '19 at 02:30

score 0 · Answer 1 · answered Apr 07 '19 at 03:14

You did not attach any parameter to your SqlCommand. You are basically running a string that looks like - "exec spAddEmployee 'nameValue', 'genderValue', 'salaryValue', @EmployeeId". Your SqlCommand has never been told that @EmployeeId is and Int Output Parameter.

Also, and more importantly, your program is at an extremely high risk of SQL Injection. If a user would populate txtName with something like "; DROP TABLE tblEmployees;--" You would probably be really upset to find your tblEmployees table dropped.

So, if you'd like to go about this the right and safe way. You should do something like:

using (SqlConnection con = new SqlConnection(cs)) {
    using (SqlCommand cmd = new SqlCommand("spAddEmployee", con)) {

        string name = txtEmployeeName.Text;
        string gender = ddlGender.SelectedValue;
        int salary = Convert.ToInt32(txtSalary.Text);

        SqlParameter op = new SqlParameter("@EmployeeId", SqlDbType.Int);
        op.Direction = System.Data.ParameterDirection.Output;

        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = name;
        //add for your other variables here
        cmd.Parameters.Add(op);

        con.Open();
        cmd.ExecuteNonQuery();
     }
}

If for some reason you insist on opening your program to SQL injection, you would at least need to add "DECLARE @EmployeeId INT " to your cmd string.

Must declare the scalar variable Error while executing stored procedure

1 Answers1