locked
How to use parameter with IN operator in SQL query RRS feed

  • Question

  • User1395831461 posted

    I am trying to use a parameter in a query in conjunction with an IN operator.  I'm finding that the query works when the parameter works out to as single value...

    EOOI

    ...returning the expected dataset but when the parameter works out to be multiple values...

    All, Customer Service, EOOI, Finance, Supply Chain

    ...the result set is returned empty.  There is no sql exception thrown just no result set returned.  The value of the parameter is built by loading the content of the items  of  a dropdownlist into a string and then loading the string into the parameter (if the 'All' option of the dropdownlist is selected) or just using the one selected value of the dropdownlist (if the 'All' selection of the dropdownlist is not selected).

    My code is below...Can someone kindly show me what I need to do to get this to work?  Thanks tonnes for any help, Roscoe

    The parameter is @YTDChartOBU .  The Query looks like...

               string query = "with" +
                                " CTE_Amount as" +
                                " (" +
                                " select CAST([Initiative] AS NVARCHAR(MAX)) as Initiative, sum(Amount) as TotalAmount" +
                                " from SavingsDetail where Year = @YTDChartYear" +
                                " and CAST([Originating_Business_Unit] AS NVARCHAR(MAX)) in (@YTDChartOBU)" +
                                " and Month between @YTDChartStartMonth and @YTDChartEndMonth" +
                                " group by CAST([Initiative] AS NVARCHAR(MAX))" +
                                " )," +
                                " CTE_Baseline as" +
                                " (" +
                                " select CAST([Initiative] AS NVARCHAR(MAX)) as Initiative, sum(BaselineAmount) as TotalBaselineAmount" +
                                " from Baselines where Year = @YTDChartYear" +
                                " and CAST([Originating_Business_Unit] AS NVARCHAR(MAX)) in (@YTDChartOBU)" +
                                " group by CAST([Initiative] AS NVARCHAR(MAX))" +
                                " )" +
                                " select" +
                                " coalesce(a.Initiative, b.Initiative) as Initiative," +
                                " IsNull(a.TotalAmount, 0)," +
                                " IsNull(b.TotalBaselineAmount, 0)" +
                                " from CTE_Amount as a" +
                                " full join CTE_Baseline as b" +
                                " on b.Initiative = a.Initiative" +
                                " order by" +
                                " Initiative";

    ...YTDChartOBU is dropdownlist and the build of the parameter @YTDChartOBU looks like...

                    String Y = YTDChartYear.Text;
                    String S = YTDChartStartMonth.Text;
                    String E = YTDChartEndMonth.Text;
                    String B = YTDChartOBU.Text;
                    String Bx = "";

                    try
                    {
                        cmd.Parameters.Add("@YTDChartYear", SqlDbType.Int).Value = Y;
                        cmd.Parameters.Add("@YTDChartStartMonth", SqlDbType.Int).Value = S;
                        cmd.Parameters.Add("@YTDChartEndMonth", SqlDbType.Int).Value = E;
                        if (B == "All")
                        {
                            foreach (ListItem li in YTDChartOBU.Items)
                            {
                                Bx = Bx + li.Value + ", ";
                            }
                            Bx = Bx.TrimEnd(' ');
                            Bx = Bx.TrimEnd(',');
                            cmd.Parameters.Add("@YTDChartOBU", SqlDbType.NVarChar).Value = Bx;
                        }
                        else
                        {
                            cmd.Parameters.Add("@YTDChartOBU", SqlDbType.NVarChar).Value = B;
                        }             
                        DataTable dt = new DataTable();
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            cmd.CommandType = CommandType.Text;
                            cmd.Connection = cn;
                            sda.SelectCommand = cmd;
                    try
                    {
                            sda.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        // Handle the error
                        if (ex.Source != null)
                            Console.WriteLine("IOException source: {0}", ex.Source);
                        throw;
                    }

    Wednesday, May 27, 2015 4:20 PM

All replies

  • User1395831461 posted

    Thanks for the reply Mikesdotnetting I read your article at http://www.mikesdotnetting.com/article/116/parameterized-in-clauses-with-ado-net-and-linq   ...building the array of parameters is a great technique - it took me a few reads to understand what you were doing and try it out in my application.  My issue now is that my query uses more than just the parameters used by the IN operator and not just that but the parameters are of mixed type...the parameters used by the IN operator are nVarchar and the other parameters are int.

    Could you show me how to specify the individual int parameters in the query and specify  the array of parameters where needed for the IN operator?  Perhaps something like...

                    string query = "with" +
                                    " CTE_Amount as" +
                                    " (" +
                                    " select CAST([Initiative] AS NVARCHAR(MAX)) as Initiative, sum(Amount) asTotalAmount" +
                                    " from SavingsDetail where Year = @YTDChartYear" +
                                    " and CAST([Originating_Business_Unit] AS NVARCHAR(MAX)) in ({0})" +
                                    " and Month between @YTDChartStartMonth and @YTDChartEndMonth" +
                                    " group by CAST([Initiative] AS NVARCHAR(MAX))" +
                                    " )," +
                                    " CTE_Baseline as" +
                                    " (" +
                                    " select CAST([Initiative] AS NVARCHAR(MAX)) as Initiative, sum(BaselineAmount) as TotalBaselineAmount" +
                                    " from Baselines where Year = @YTDChartYear" +
                                    " and CAST([Originating_Business_Unit] AS NVARCHAR(MAX)) in ({0})" +
                                    " group by CAST([Initiative] AS NVARCHAR(MAX))" +
                                    " )" +
                                    " select" +
                                    " coalesce(a.Initiative, b.Initiative) as Initiative," +
                                    " IsNull(a.TotalAmount, 0)," +
                                    " IsNull(b.TotalBaselineAmount, 0)" +
                                    " from CTE_Amount as a" +
                                    " full join CTE_Baseline as b" +
                                    " on b.Initiative = a.Initiative" +
                                    " order by" +
                                    " Initiative";

    ...using both parameters individually specified and arrays of parameters?  Sorry if I'm missing something or my question is poorly put...I'm still a bit of a newbie at this,

    Thanks tonnes for your help, Roscoe

    Thursday, May 28, 2015 2:37 PM