how to pass string parameter to sql SP with ado.net command in c#?

Farshad Valizade 381

hi every body.

I have a big problem with SP in sql server.

I have a SP that join 3 table and I pass two parameters to it . one for column name another for it's value.

ALTER procedure [dbo].[GetAllLine](@column nvarchar(10) , @value int)
as
begin
select * from 
Joint as j
inner join Line as l on l.LineID =j.LineID
inner join Sheet as s on s.LineID=s.LineID
where @column = @value
end

and my c# code:


        public static DataTable GetAll(string column , int value)
        {
            string query = "GetAllLine";
            SqlCommand cmd = new SqlCommand(query, Connection.Conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@column", column);
            cmd.Parameters.AddWithValue("@value", value);

            DataTable dt = new DataTable();
            Connection.Open();
            try
            {
                SqlDataReader rd = cmd.ExecuteReader();
                dt.Load(rd);
                return dt;
            }
            catch
            {
                throw;
            }
            finally
            {
                Connection.Close();
            }
        }

and my command btn :

DataTable dt = Test.GetAll("j.LineID", 2);
            dataGridView1.DataSource = dt;

I want use one query with different column name to pass to this SP.

exmalpe :

Test.GetAll("l.LineID", 21);

Test.GetAll("s.SheetId", 12);

after run get this error:

Conversion failed when converting the nvarchar value 'j.LineID' to data type int.

How Should I Pass String Column with Int Value to this SP?

Jiale Xue - MSFT 34,276 Microsoft Vendor

Hi @Farshad Valizade , Welcome to Microsoft Q&A.

The problem is with the column name parameter (@column) you pass. You're passing the column name as a string to the stored procedure, and then trying to compare it to an integer value within the stored procedure causes a type conversion error.

In a SQL stored procedure, you cannot directly pass a column name as a parameter to a query, because it will be treated as a string instead of an actual column name. You need to use dynamic SQL to build your query and insert the column names into the query.

Try to use

CREATE PROCEDURE [dbo].[GetAllLine]
    @column nvarchar(128),
    @value int
AS
BEGIN
    DECLARE @sql nvarchar(max);

    SET @sql = N'
        SELECT *
        FROM Joint AS j
        INNER JOIN Line AS l ON l.LineID = j.LineID
        INNER JOIN Sheet AS s ON s.LineID = s.LineID
        WHERE ' + QUOTENAME(@column) + N' = @value';

    EXEC sp_executesql @sql, N'@value int', @value = @value;
END

public static DataTable GetAll(string column, int value)
{
    DataTable dt = new DataTable();

    using (SqlConnection conn = new SqlConnection("YourConnectionString"))
    {
        using (SqlCommand cmd = new SqlCommand("GetAllLine", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter paramColumn = new SqlParameter("@column", SqlDbType.NVarChar, 128);
            paramColumn.Value = column;
            cmd.Parameters.Add(paramColumn);

            SqlParameter paramValue = new SqlParameter("@value", SqlDbType.Int);
            paramValue.Value = value;
            cmd.Parameters.Add(paramValue);

            conn.Open();

            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                dt.Load(reader);
            }
        }
    }

    return dt;
}

//Use like this
DataTable dt1 = GetAll("j.LineID", 2);

Farshad Valizade 381 Reputation points

2023-05-23T11:06:10.09+00:00

Thanks Man . I use your code but it get this error:

System.Data.SqlClient.SqlException: 'Invalid column name 'j.LineID'.'

Jiale Xue - MSFT 34,276 Microsoft Vendor

Try to use:

CREATE PROCEDURE [dbo].[GetAllLine]
    @columnName nvarchar(128),
    @value int
AS
BEGIN
    DECLARE @sql nvarchar(max);

    IF @columnName = 'LineID' OR @columnName = 'SomeOtherColumn'
    BEGIN
        SET @sql = N'
            SELECT *
            FROM Joint AS j
            INNER JOIN Line AS l ON l.LineID = j.LineID
            INNER JOIN Sheet AS s ON s.LineID = j.LineID
            WHERE ' + QUOTENAME(@columnName) + N' = @value';
    
        EXEC sp_executesql @sql, N'@value int', @value = @value;
    END
    ELSE
    BEGIN
        -- Handle invalid column name error
        RAISERROR('Invalid column name', 16, 1);
    END
END

public static DataTable GetAll(string columnName, int value)
{
    DataTable dt = new DataTable();

    using (SqlConnection conn = new SqlConnection("YourConnectionString"))
    {
        using (SqlCommand cmd = new SqlCommand("GetAllLine", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter paramColumn = new SqlParameter("@columnName", SqlDbType.NVarChar, 128);
            paramColumn.Value = columnName;
            cmd.Parameters.Add(paramColumn);

            SqlParameter paramValue = new SqlParameter("@value", SqlDbType.Int);
            paramValue.Value = value;
            cmd.Parameters.Add(paramValue);

            conn.Open();

            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                dt.Load(reader);
            }
        }
    }

    return dt;
}

// Usage
DataTable dt1 = GetAll("LineID", 2);

Farshad Valizade 381 Reputation points

2023-05-24T13:11:48.51+00:00
tnx again.

I use above code when I use : DataTable dt1 = GetAll("LineID", 2); It gets error : Ambiguous column name 'LineID'

when I change it to

DataTable dt1 = GetAll("j.LineID", 2);

then get error : Invalid column name
Naomi 7,361 Reputation points

2023-05-24T21:53:54.6233333+00:00

Remove quotename from the procedure then.

1 answer

Olaf Helper 41,001 Reputation points

2023-05-22T07:20:22.83+00:00

Conversion failed when converting the nvarchar value 'j.LineID' to data type int.

As the error says, you have to use the right data types.
Please sign in to rate this answer.
Farshad Valizade 381 Reputation points

2023-05-23T03:28:50.45+00:00

Thanks Man . I use your code but it get this error:

System.Data.SqlClient.SqlException: 'Invalid column name 'j.LineID'.'
Sign in to comment