Must declare the scalar variable in Select command

Question

I am passing a long list of employeeIds to employeeIdlist and I split them into a List. Using this list I am adding parameters to my query. I am getting the following error

{"Must declare the scalar variable \"@EmployeeId\"."}

public List<versionInfo> GetVersion(string  employeeIdlist)
{
    DbHelper helper = new DbHelper();
    List<versionInfo> empVerInfo = new List<versionInfo>();

    using (SqlConnection conn = new SqlConnection(connString))
    {              
        conn.Open();
        using (SqlCommand getVersion = new SqlCommand())
        {
            getVersion.Connection = conn;
            getVersion.CommandText = @"SELECT EmployeeId,Version 
                                                FROM [dbo].[EmployeeVersion]
                                                WHERE EmployeeId in (@EmployeeId)";

            getVersion.CommandType = CommandType.Text;
            List<int> empIds = employeeIdlist.Split(',').Select(int.Parse).ToList();
            StringBuilder sb = new StringBuilder();
            int i = 0;
            foreach (var emp in empIds)
            {
                // IN clause
                sb.Append("@EmployeeId" + i.ToString() + ",");
                // parameter
                getVersion.Parameters.AddWithValue("@EmployeeId" + i.ToString(), emp);
                i++;
            }

           // getVersion.Parameters.AddWithValue("@EmployeeId", employeeIdlist);
            SqlDataReader rdr = getVersion.ExecuteReader();
            while (rdr.Read())
            {
                versionInfo vi = new versionInfo();
                vi.employeeId = helper.GetDb<int>(rdr, "EmployeeId");
                vi.version = helper.GetDb<decimal>(rdr, "Version");
                empVerInfo.Add(vi);                      
            }
            rdr.Close();
        }
        conn.Close();
    }
    return empVerInfo;
}

You are adding multiple parameter by keeping your AddWithValue inside the loop; Use String.Join to concatenate the list and finally add only one parameter — techspider, Aug 18 '16 at 20:20
I would suggest moving the sql over into a stored procedure and declaring a input param named `@EmployeeId` you have a list of EmployeeId's I would also make a type and pass in the List to let the Database handle the IN Clause are you familiar with creating user types in sql server..? — MethodMan, Aug 18 '16 at 20:22

score 5 · Accepted Answer · answered Aug 18 '16 at 20:23

Remove the text after the IN

getVersion.CommandText = @"SELECT EmployeeId,Version 
                          FROM [dbo].[EmployeeVersion]
                          WHERE EmployeeId in (";

then the foreach could build the full list of parameters and texts

foreach (var emp in empIds)
{
    sb.Append("@EmployeeId" + i.ToString() + ",");
    getVersion.Parameters.AddWithValue("@EmployeeId" + i.ToString(), emp);
    i++;
}

after exiting the loop remove the last comma from the StringBuilder

sb.Length--;

finally, complete the command text appending the content of the StringBuilder and do not forget the closing parenthesys for the IN clause.

getVersion.CommandText += sb.ToString() + ")";

Now you can run the command with the correct IN clause and a matching list of parameters

score 1 · Answer 2 · edited May 23 '17 at 12:33

If fails because your string query has one parameter named @EmployeeId and your Command object has many parameters with different names ("@EmployeeId1" is not equal to "@EmployeeId")

It seems like you are trying to apply this approach, which is a good idea.

You are two lines away of getting it to work:

Add this lines:

sb.Lenght--;
getVersion.CommandText = getVersion.CommandText.Replace("@EmployeeId",sb.ToString())

just before:

SqlDataReader rdr = getVersion.ExecuteReader();

After doing that your added parameters will match those @parameters existing in the sql string.

score 1 · Answer 3 · answered Aug 18 '16 at 20:32

This is just another option. You can achieve the same result in 3 lines of code using Dapper ORM used in Stack Overflow.

You can download via NuGet.

public class VersionInfo
{
    public int EmployeeId { get; set; }
    public decimal Version { get; set; }
}

class Program
{
    public static string connString = "...";

    static void Main(string[] args)
    {
        var result = GetVersion(new List<int> {1, 2});
        Console.ReadLine();
    }

    public static List<VersionInfo> GetVersion(IList<int> employeeIds)
    {
        using (IDbConnection conn = new SqlConnection(connString))
        {
            conn.Open();
            var entities = conn.Query<VersionInfo>(
                @"SELECT EmployeeId, Version from EmployeeVersion WHERE EmployeeId IN @EmployeeIds",
                new {EmployeeIds = employeeIds});

            return entities.ToList();
        }
    }
}

score 0 · Answer 4 · answered Aug 18 '16 at 20:19

0

On your select statement you have to declare a value for your variable. I have made it an Integer. If it is a text value, then you can use varchar(25).

@"DECLARE @EmployeeId INT

SELECT EmployeeId,Version 
FROM [dbo].[EmployeeVersion]
WHERE EmployeeId in (@EmployeeId)";

answered Aug 18 '16 at 20:19

bwilliamson

391
1
13

The OP is trying to search inside a list of Employees. See `empIds` variable. – Gerardo Grignoli Aug 18 '16 at 20:30

Must declare the scalar variable in Select command

4 Answers4