Inserting Records Conditionally

Posted on January 10, 2012 By jollyjollybean

I faced a typical problem at work not too long ago dealing with inserting conditional data into a table. Typically I would write a bit of code to handle the conditional logic, but I thought about the problem for a bit and came up with a different solution using SQL. Here is the background of the problem:

I have a User table used for authentication purposes with a web application. This table contains information about the user such as name, email, etc. There is a separate table of Roles that can be assigned to the user. There is also child table to the User table that relates the User to the Roles that have been assigned named User_Roles. Pretty simple. There is another child table that is used to maintain the User's currently selected Role named Selected_Role.

In creating the administration pages to handle authorizing users and assigning roles, I needed functionality to automatically set the Selected_Role entry for the user when they were first assigned a role from the Role table. But, if they already had a selected role, i did not want to do anything.

My first thought was to add some logic to check if the user already had an entry in the Selected_Role table and then perform the insert. But then I came up with this:

insert into Selected_Role(UserID, RoleID)
select
	@UserID,
	@RoleID
where
	(select COUNT(*) from Selected_Role where UserId = @UserID) = 0

This rather simple statement will do the following depending on the result of the subquery in the where clause:

If the (select COUNT(*) from Selected_Role where UserId = @UserID) result returns 0, meaning there are no entries for the user, it will insert one row.

If the (select COUNT(*) from Selected_Role where UserId = @UserID) result returns 1, meaning the user already has a selected role, it will not insert a row.

You can continue adding more where clauses to add additional conditional logic to the insert. Say you wanted to also restrict this logic to only insert a selected role to users that have no selected role and have a gmail email address:

insert into Selected_Role(UserID, RoleID)
select
	@UserID,
	@RoleID
where
	(select COUNT(*) from Selected_Role where UserId = @UserID) = 0
	and (select email from Users where UserID = @UserID) like '%@gmail.com%'

SQL Conditional, Insert, Microsoft SQL Server