Inline T-SQL OR subquery

correlated subquery
inline query in sql server
inline subquery in sql server
inline subquery in oracle example
inline queries in sql w3schools
sql multiple subqueries in (select statement)
sql subquery join
inline query vs subquery

Trying to figure out what would be the most efficient approach to use a subquery in an inline SQL statement. Inline SQL is not something I have much experience with, but I have no choice at my organization alas.

SELECT * 
FROM dbo.VW_RMISPayment
WHERE ProcDate BETWEEN '7/2/2018' AND '3/8/2019' 
  AND Status = 'P' 
  AND Fund = '359' 
  AND Amount > 0 
  AND (BatchNotate = 'B' OR BatchNotate IS NULL)
ORDER BY ProcDate, Amount

How could I parameterized the (BatchNotate = 'B' OR BatchNotate IS NULL) part?

My variable passed in as a List<string>, but I could change it to be anything. I'm just not sure how I can create this subquery from my variable

if (BatchNotate.Count() > 0)
{
    query += " AND BatchNotate= @BatchNotate";
}

 cmd.Parameters.AddWithValue("@BatchNotate", batchNotate);

Use this:

BatchNotate = COALESCE(@inVariable,'B')

If the variable (@inVariable) is null then it will "default to B.

If it is something else it will compare against that.

Using Subqueries in the Select Statement (with examples , In this case, you can think of the subquery as a single value expression. The result returned is no different than the expression “2 + 2.” Of course, subqueries can� Inline Subqueries. An introduction to inline subqueries. In a previous section we built subqueries using the keyword WITH. Another kind of subquery is an inline subquery. In a standard inline subquery, the subquery is a component of a FROM clause, taking the place of a table name. A caveat of this subquery is that it must must be uncorrelated

Could you do something like this?

SELECT * 
FROM dbo.VW_RMISPayment
WHERE ProcDate BETWEEN '7/2/2018' AND '3/8/2019' 
  AND Status = 'P' 
  AND Fund = '359' 
  AND Amount > 0 
  AND BatchNotate = COALESCE(@BatchNotate, BatchNotate)
ORDER BY ProcDate, Amount

The Basics of Inline View in Oracle By Examples, The subquery specified in the FROM clause of a query is called an inline view. Because an inline view can replace a table in a query, it is also called a derived� The SQL subquery syntax. There is no general syntax; subqueries are regular queries placed inside parenthesis. Subqueries can be used in different ways and at different locations inside a query: Here is a subquery with the IN operator.

Neither of those worked for what I'm after. Here is what I ended up doing. It's kinda hacky, but it works.

 public static string AddParametersOR<T>(SqlParameterCollection parameters,
                                string fieldName,
                                string pattern,
                                SqlDbType parameterType,
                                int length,
                                IEnumerable<T> values)
{
    if (parameters == null)
        throw new ArgumentNullException("parameters");
    if (pattern == null)
        throw new ArgumentNullException("pattern");
    if (values == null)
        throw new ArgumentNullException("values");
    if (!pattern.StartsWith("@", StringComparison.CurrentCultureIgnoreCase))
        throw new ArgumentException("Pattern must start with '@'");

    var parameterNames = new List<string>();
    foreach (var item in values)
    {
        var parameterName = parameterNames.Count.ToString(pattern, CultureInfo.InvariantCulture);
        string parameterWithFieldName = string.Empty;
        if (item.ToString().ToUpper() == "NULL")
        {
            parameterWithFieldName = string.Format("{0} IS NULL", fieldName);
        }
        else if (item.ToString().ToUpper() == "NOTNULL")
        {
            parameterWithFieldName = string.Format("{0} IS NOT", fieldName);
        }
        else
        {
            parameterWithFieldName = string.Format("{0}= {1}", fieldName, parameterName);
        }
        parameterNames.Add(parameterWithFieldName);
        parameters.Add(parameterName, parameterType, length).Value = item;
    }

    return string.Join(" OR ", parameterNames.ToArray());
}

Usage:

if (batchNotate.Count() > 0)
 {
    query += " AND ({@BatchNotate})";
 }

string batchNotateParamNames = SqlHelper.AddParametersOR(cmd.Parameters, "BatchNotate", "@B0", SqlDbType.VarChar, 1, batchNotate);

cmd.CommandText = query.Replace("{@BatchNotate}", batchNotateParamNames);

Depending on how many items are in your list the output will look like this:

(BatchNotate= 'B' OR BatchNotate= 'N' OR BatchNotate IS NULL)

If it finds "NULL" or "NOTNULL" it will replace these with IS NULL or IS NOT NULL

What is the difference between an inline query and a sub-query in , SubQuery: A query within another SQL query and embedded within the WHERE clause. A subquery is used to return data that will be used in the main query as� A correlated subquery is a SELECT statement nested inside another T-SQL statement, which contains a reference to one or more columns in the outer query. Therefore, the correlated subquery can be said to be dependent on the outer query. This is the main difference between a correlated subquery and just a plain subquery. A plain subquery is not

Subquery Inline View - Nested subquery, Advanced Oracle SQL: Subquery Inline View. Oracle Tips by Laurent Schneider. Laurent Schneider is considered one of the top Oracle SQL experts, and he is the � The subquery has been aliased with the name subquery2. This will be the name used to reference this subquery or any of its fields. The trick to placing a subquery in the select clause is that the subquery must return a single value. This is why an aggregate function such as the SUM, COUNT, MIN, or MAX function is commonly used in the subquery.

SQL Subquery, IN SELECT - with Examples, The SQL subquery syntax. There is no general syntax; subqueries are regular queries placed inside parenthesis. Subqueries can be used in different ways and at� A subquery SELECT statement can return any number of values, and can be found in, the column list of a SELECT statement, a FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T-SQL statement. A Subquery can also be used as a parameter to a function call. Basically a subquery can be used anywhere an expression can be used.

Subqueries (SQL Server), Look at an example of a subquery, which is a query that is nested in a SELECT, INSERT, UPDATE, or DELETE statement, or inside another� Without any DDL statements defining the tables it is difficult to test but: You are comparing dates using strings - Oracle is probably doing an implicit conversion back to a date using the value of the NLS_DATE_FORMAT session parameter (which is hopefully DD-MON-YY) but if this ever changes then it will break the query (and the query won't have changed so it will be a pain to debug).

Comments
  • You may want to study up on table-valued parameters here. Then you could use something like ... where ... and ( exists ( select 42 from @BatchNotations as BN where BN.BatchNotate = VW_RMISPayment.BatchNotate ) or VW_RMISPayment.Batch_Notate is NULL ) ... where @BatchNotations is a table-valued parameter with one row for each value you want to process.
  • This does not make sense to me the COALESCE(@BatchNotate, NULL) has no functionality.
  • I edited my answer. Does that make more sense? It is very possible I am not understanding the question properly.
  • Yes @Isaac that fixes the bug, I'm not sure it is the functionality the OP wants but at least it makes sense.
  • this solution is ripe for an SQL injection attack
  • Please explain further why ??? AddParameters is limiting the param to Varchar(1) - So a attack is going to take place with 1 character ?
  • sure -- if it is called with field name set to 1=1); DROP TABLE dbo.VW_RMISPayment; -- and item set to null or notnull
  • I can see that however fieldName is set programmatically and not accessible to the end-user.
  • I question that -- many injection attacks are performed by changing configuration files and such. You should not be taking any variable and insertiting it into a query