How to add column in SELECT statement dynamically in tSQL based on condition?

I am creating a tSql with parameters and based on some condition I want to add column in SELECT statement and I am not sure how to do it.

My Logic:

var keywordClause = keyword.IsNotEmpty()
                    ? "[Name] like '%" + keyword + "%'"
                    : "1 = 1";
// keywordClause  = "[Name] like '%Test%'"

var orderBy = sortParameters.ToOrderBy();
// orderBy =  "Name ASC"

var parameters = new List<SqlParameter>
{
    new SqlParameter("@Keyword", keywordClause),
    new SqlParameter("@OrderBy", orderBy)
};

var sql = string.Format(@"SELECT Id,
                                 Name,
                                 CreateDateTime Created
                          FROM CallCenter WITH (NOLOCK)
                          WHERE(IsDeleted = 0)
                          AND @Keyword 
                          ORDER BY @OrderBy");

return _PageList(ctx => ctx.CallCenterSummaries.SqlQuery(sql, parameters.ToArray())
                .AsQueryable()
                .Select(d => d.TrimSpaces()), page, pageSize);

Based on my logic, I am expecting this query:

SELECT Id,
       Name,
       CreateDateTime Created
FROM CallCenter WITH (NOLOCK)
WHERE(IsDeleted = 0)
AND [Name] like '%Test%'
ORDER BY Name ASC;

Here I am getting "An expression of non-boolean type specified in a context where a condition is expected, near 'ORDER'." exception and I believe it is taking Name as a string. How can I get desired results?

1 answer

  • answered 2018-03-13 21:23 Vidmantas Blazevicius

    You can't have SqlParameter as an entire clause - it needs to be right hand side of the clause. For your scenario - the below should work.

    var parameters = new List<SqlParameter>
    {
        new SqlParameter("@Keyword", keyword.IsNotEmpty() ? keyword : "%")
    };
    
    var sql = string.Format($@"SELECT Id,
                                     Name,
                                     CreateDateTime Created
                              FROM CallCenter WITH (NOLOCK)
                              WHERE(IsDeleted = 0)
                              AND [Name] like @Keyword 
                              ORDER BY {orderBy}");
    

    EDIT: I've edited my code sample to use order by as a value in the interpolated string rather than as a SqlParameter, however, this does open you up to a Sql injection if the value of the orderBy is influenced by user's inputs.

    Alternatively, I would recommend not using ORDER BY clause at all in your query and sorting results in memory before passing them back for display.