Select and Set Value in SQL Statement

Question

Select and Set Value in SQL Statement

c# dapper sql sql-server

Question

I have a rather quirky question. In a SELECT statement can I set a default value?

In the following query I want boolItem to always be false (not retrieved from the database). Crazy I know but bear with me as I explain why.

SELECT id, boolItem = False
FROM MyTable;

I am working with a large existing SQL database and project. I am querying data and returning them as Action C# objects. An action can be custom made by the user or a standard one. This is signified by a property IsCustom.

public class Action
{
    public int Id { get; set; }
    public bool IsCustom { get; set; }
    .....
}

In the SQL database custom actions are stored in the table custom_actions and standard actions are in the table actions.

I retrieve and store Action objects using the below code. I want to make the query of the actions table always set the property IsCustom to false. And the query of the custom_actions table always set the property IsCustom to true. I am using the query SELECT a.id AS Id, a.is_custom = false AS IsCustom which is not valid code because the table doesn't have a is_custom column but its to demonstrate what I am trying to do.

public async Task<IEnumerable<Models.Action>> ExecuteAsync (IDbConnection conn, IDbTransaction transition, long userId)
{
    string sql = @"SELECT a.id AS Id, a.is_custom = false AS IsCustom
                   FROM actions a
                   INNER JOIN members_actions ma ON  a.id = ma.action_id AND is_custom = false
                   WHERE ma.member_id = :userId
                   UNION
                   SELECT a.id AS Id, a.is_custom = true AS IsCustom 
                   FROM custom_actions a
                   INNER JOIN members_actions ma ON  a.id = ma.action_id AND is_custom = true
                   WHERE ma.member_id = :userId;";

    return await conn.QueryAsync<Models.Action> (sql, new {userId = userId}, transition);
}

Table 'Actions' Columns = id || description || name
Table 'Custom_actions' Columns = id || description || name || parameters

Is this possible? Its better than structurally changing the database (merging the 2 tables into 1 and adding an is_custom column).

sazr

View more on Stack Overflow

Adam Porad · Accepted Answer · 7/9/2015 3:27:58 AM

You can just select the value true or false and use an alias to specify the the column name IsCustom

For example, I modified your example below to show how to do it (and also removed AND is_custom = false/true from the JOIN conditions because it didn't appear that there is an is_custom column in either table).

public async Task<IEnumerable<Models.Action>> ExecuteAsync (IDbConnection conn, IDbTransaction transition, long userId)
{
    string sql = @"SELECT a.id AS Id, false AS IsCustom
                   FROM actions a
                   INNER JOIN members_actions ma ON  a.id = ma.action_id 
                   WHERE ma.member_id = :userId
                   UNION
                   SELECT a.id AS Id, true AS IsCustom 
                   FROM custom_actions a
                   INNER JOIN members_actions ma ON  a.id = ma.action_id 
                   WHERE ma.member_id = :userId;";

    return await conn.QueryAsync<Models.Action> (sql, new {userId = userId}, transition);
}

Select and Set Value in SQL Statement

Question

Fastest Dapper Plus Extensions

Accepted Answer

Performance

Expression Evaluator