使用storeprocedure时，Sql查询无法获取in子句中的值 [英] Sql query could not fetch value on in clause when storeprocedure is used

问题描述

我创建了sql查询，它在普通数据集中工作，当我尝试使用存储过程使用相同的查询时，结果不会获取值。

I have created sql query which works in normal dataset, When iam trying to use same query using store procedure, result does not fetch the value.

string datatypeval="'first name','address'";
 string FieldNames1 = "select FId,DataTypeName from tbl_fieldname where DataTypeName in(" + datatypeval + ");";

以上查询工作正常。



使用存储过程时，它不会获取输出值。

Above query works perfectly.

When using store procedure, it doesnt fetch output value.

DataTable dt1 = dataval(datatypeval);


public static DataTable dataval(string datatypeval)
    {
        try
        {
            DataTable dt1;
            string[] ColumnName = new string[1];
            string[] values = new string[1];
            ColumnName[0] = "@dataval"; values[0] = datatypeval;
            dt1 = returnDatatable("datatype_Details", ColumnName, values);
            return dt1;
        }
        catch (Exception e)
        {
            throw e;
        }
    }
public static DataTable returnDatatable(string SPName, string[] ParameterName, string[] Values)
    {
        DataTable ds = new DataTable();
        try
        {
            openDBConnection();
            SqlConnection mssqlConn = mssqlConnection;
            SqlCommand Cmd = new SqlCommand(SPName, mssqlConn);
            SqlDataAdapter da = new SqlDataAdapter(Cmd);
            Cmd.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < ParameterName.Length; i++)
            {
                Cmd.Parameters.AddWithValue(ParameterName[i], Values[i]);
            }
            da.Fill(ds);
        }
        catch (Exception ex)
        {
           throw ex;
        }
        finally
        {
            closeDBConnection();
        }
        return ds;
    }

我的SP代码：

My SP Code:

ALTER PROCEDURE [dbo].[datatype_Details]
(
@dataval VARCHAR 
) 
AS
BEGIN
	 
	select FId,DataTypeName from tbl_fieldname  where DataTypeName in(@dataval)
END

我的尝试：

What I have tried:

DataTable dt1 = dataval(datatypeval);
public static DataTable dataval(string datatypeval)
    {
        try
        {
            DataTable dt1;
            string[] ColumnName = new string[1];
            string[] values = new string[1];
            ColumnName[0] = "@dataval"; values[0] = datatypeval;
            dt1 = returnDatatable("datatype_Details", ColumnName, values);
            return dt1;
        }
        catch (Exception e)
        {
            throw e;
        }
    }
public static DataTable returnDatatable(string SPName, string[] ParameterName, string[] Values)
    {
        DataTable ds = new DataTable();
        try
        {
            openDBConnection();
            SqlConnection mssqlConn = mssqlConnection;
            SqlCommand Cmd = new SqlCommand(SPName, mssqlConn);
            SqlDataAdapter da = new SqlDataAdapter(Cmd);
            Cmd.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < ParameterName.Length; i++)
            {
                Cmd.Parameters.AddWithValue(ParameterName[i], Values[i]);
            }
            da.Fill(ds);
        }
        catch (Exception ex)
        {
           throw ex;
        }
        finally
        {
            closeDBConnection();
        }
        return ds;
    }

推荐答案

请看这里：在SQL IN子句中使用逗号分隔值参数字符串 [ ^ ]

这篇关于使用storeprocedure时，Sql查询无法获取in子句中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！