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 = "";
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,
Member
108 Points
294 Posts
How to use parameter with IN operator in SQL query
May 27, 2015 04:20 PM|rpfinnimore|LINK
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;
}
All-Star
186966 Points
27086 Posts
Moderator
Re: How to use parameter with IN operator in SQL query
May 27, 2015 04:34 PM|Mikesdotnetting|LINK
You can do this: http://www.mikesdotnetting.com/article/116/parameterized-in-clauses-with-ado-net-and-linq or this: http://www.codeproject.com/Tips/584680/Using-comma-separated-value-parameter-strings-in-S
ASP.NET Tutorials | Learn Entity Framework Core | Learn Razor Pages
Member
108 Points
294 Posts
Re: How to use parameter with IN operator in SQL query
May 28, 2015 02:37 PM|rpfinnimore|LINK
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