Using a Case Statement in a SQL Where Clause

It is common knowledge that you can apply a Case statement in the SELECT or ORDER BY portion of a SQL statement. What isn’t well known is that you can use it effectively in a WHERE clause.

Take an example I just ran into:

I want to show all orders to every employee unless the order is assigned to employeeid 263, EXCEPT if the person trying to view all orders IS employeeid 263 or if it is that persons boss. In that case, show every order.

This appears really simple and could be handled in a where clause, but I couldn’t figure it out. And even if there is a better way, why not use a CASE statement just for fun anyway:

select * from Orders
where
case
when @loginid = 263 or @loginid = 181
then employeeid
when (@loginid <> 263 and @loginid <> 181) and employeeid = 263
then 0
else employeeid
end
= employeeid

The first when says “if you are #263 or #181, set employeeid = employeeid, which will always be true and will the order.

The second when says, “if you aren’t #263 or #181, and the current order is assigned to #263, then set t.employeeid = 0” which will always be false, and never show the order.

The tricky part of adapting the Case statement is realizing you are only setting one side of an equation, and the second part, the “= employeeid” follows after the “end”.

Alphabetizing Words in a Phrase Using SQL

I had the need to today to alphabetize a phrase using SQL. For example, I needed “red cowboy pretty hat” to read “cowboy hat pretty red”. Here is the code I wrote to do so:

declare @input varchar(100)

set @input = "red cowboy pretty hat"

--declarations
DECLARE @i            int
        ,@len        int
        ,@word        varchar(100)
        ,@char        varchar(1)
        ,@newphrase    varchar(1000)

--Variable/Constant initializations
SET @i = 1
SET @len = LEN(@input)
SET @word = ""
SET @newphrase = ""

create table #temp(word varchar(100))

WHILE @i < @len
  begin

    SET @char = SUBSTRING(@input, @i, 1)
    IF @char = " "
      begin
            --add word to temp table
            if len(@word) > 0
                insert into #temp(word)
                select @word

            SET @word = ""
      end
    else
        SET @word = @word + @char

    SET @i = @i + 1
  end

select @newphrase = @newphrase + " " + word
from #temp
order by word

print @newphrase

drop table #temp

Find and Delete Duplicates in SQL

We all know we shouldn’t have duplicates in the database. And despite my best efforts, somehow they sneak in from some legacy code, or from the hyper-active-compulsory-submit-the-form-fifty-times-in-five-seconds-bloke.

So now and then I find myself writing the same SQL to track them down. Most solutions online say that you have to use a temporary table or rename tables to get rid of them. This solution works without either, providing my way of eliminating the duplicates is sufficient for your needs.

Here is my code to find duplicates:

SELECT *
FROM users u JOIN (
   SELECT username, min(userid) AS minid FROM users
   GROUP BY username
   HAVING ( COUNT(username) > 1 )
) AS dupusers
ON u.username = dupusers.username
ORDER BY userid DESC

Let’s first look at the nested Select:

SELECT username, min(userid) AS minid FROM users
GROUP BY username
HAVING ( COUNT(username) > 1 )

Because I identify a record by the same username, I group on that. Once grouped on username, I can use Having to check the count for that username. If the count is > 1, then it is a duplicate of another record. I am taking the viewpoint that the first user inserted is the one we are going to keep. All users with the same username after that one is hence a duplicate. I am returning the minimum userid for each duplicate record so I know which one I will not delete later.

This query by itself would find you all the duplicates, but isn’t as handy because you can’t view all the columns of each record.

Now if we look at the full join, I join the two tables together: The nested table is the set of all users that have a duplicate in the database inner joined with the set of all users. This will give us the set of all users that have duplicates.

Ok, so how do we go about deleting the sibling duplicates of a base record and not every record that has a duplicate? In other words, if I have:

ID: 1
username: Scott
MinID: 1

ID: 2
username: Scott
MinID: 1

ID: 3
username: Scott
MinID: 1

I only want to delete records with ID’s of 2 and 3; not the ID of 1. Let’s look at my Delete syntax:

DELETE users
FROM users u JOIN (
    SELECT username, min(userid) AS minid FROM users
    GROUP BY username
    HAVING ( COUNT(username) > 1 )
) AS dupusers
ON u.username = dupusers.username
WHERE u.userid <> minid

The code is the exact same with two exceptions. One, I switched from the Select statement to a Delete. The second is the filter :

WHERE u.userid <> minid

This will delete every userid that isn’t the smallest userid for that group of duplicates. Using my example set above, ID’s 2 and 3 would not equal the minid of 1, and therefore would be deleted, leaving one non-duplicated record.

This code assumes that a row is a duplicate of another if their username is the same. In most cases, a duplicate is only a duplicate if a combination of fields are the same. This can be accommodated by changing the nested Select statement:

SELECT *
FROM users u JOIN (
    SELECT firstname, lastname, min(userid) AS minid FROM users
    GROUP BY firstname, lastname
    HAVING ( COUNT(*) > 1 )
) AS dupusers
ON u.firstname = dupusers.firstname AND u.lastname = dupusers.lastname
ORDER BY u.lastname, u.firstname

This is a lame example since there can be many people with the same name, but you get the picture. In reality, I have had to use this technique when looking for duplicate addresses. For that I grouped on address, city and zip.

So there you go…a way to nuke duplicates without using temp tables.

Using Case in Order By with Group By

Wow, that’s a mouthful. This is what I am trying to convey: When you do a Group By with Rollup, what happens is that the rolled up column shows null, and won’t necessarily be ordered at the bottom. For example:

select
    firstname
    , count(*) as total
from AdsEntered ads
join tblStaff s on s.staffid = ads.staffidgroup by firstname
with rollup
order by
total desc

This would output:

Groupbynull

Now, I want the person with the highest total at the top, but I certainly don’t want the the total row being first. So the goal is to still sort descending, yet stick the total row at the bottom:

select
    isnull(firstname, "TOTAL") as firstname
    , count(*) as total
from AdsEntered  ads join tblStaff s on s.staffid = ads.staffid
group by firstname
with rollup
order by
case when firstname is null then 0
else 1 end  desc

This would yield:

Orderbycase1

First, I used isnull(firstname, “TOTAL”) to get rid of the ugly null. Pretty simple.

Now look at the case statement in the order by. I am saying, “when the firstname column is null, then treat it like it is 0, else treat it like it is 1. Then order it descending, so the field with the 1 will go last.” Notice I wrote when firstname is null, not when firstname = ‘TOTAL’. To check for ‘TOTAL’ you would have to apply the entire line isnull(firstname, “TOTAL”) = “TOTAL”.

Now that we got the total line in the right place, we need to order all the “0” rows descending. We do this by applying another order by on the total column.

select
    isnull(firstname, "TOTAL") as firstname
    , count(*) as total
from AdsEntered  ads join tblStaff s on s.staffid = ads.staffid
group by firstname
with rollup
order by
case when firstname is null then 0
else 1 end  desc
, total desc

Yielding:

Orderbycase

Cool, huh?

Using SQL Dateparts

I don't know what my problem is with my short term memory, but certain things defy my ability to remember. Which way do I turn the faucet? Did I pass my street to the barbershop? What the heck DID I do last weekend?

In all seriousness, I was posed that last question one week after my wedding and was unable to answer for at least a minute. Oy yoy yoy.

Anyway, my point is that I have to consult SQL Books Online almost every time I need to use a Datepart of minute. I am always *pretty* sure it isn't “mm”, and I guess it is “mi”, but I still look it up. Well, this last time something clicked and I thought, “Why couldn't I just use the whole name instead?”.

So I tried it. And it worked. Yay.

So instead of this:

select dateadd(mi, 10, getdate()) 
select dateadd(mm, 1, getdate())

I can write:

select dateadd([minute], 10, getdate()) 
select dateadd([month], 1, getdate())

You don't have to use the brackets, but because those are keywords, SQL will highlight them and make my code look yucky.

Now, I realize that the hardcore will laugh and use the abbreviations anyway. And although I am not so much of a retard to HAVE to do this, I am all about not having to think when reading through someone else's code.

And since .NET uses “mm” to define “minute”, spelling it out ain't all bad.

Using SQL Where Clauses for Searching

One piece of SQL lore I take for granted is COALESCE(). The dictionary definition describes SQL's use pretty close as “To bring or come together into a united whole”. Books Online states in laymans terms that SQL allows you to pass any number of arguments to the function, and it will return the first non-null one back to you. In other words, it “brings together” many variables, and outputs just one.

For example, if a user wants to search for a record in our Customers table, they may want to search for Firstname, Lastname, Email, Phone, and Address. How do you write your SQL for this?

if len(@Address) > 0
SELECT * from Customers
WHERE address like @Address

if len(@Phone) > 0
SELECT * from Customers
WHERE phone like @Phone

--you get the picture...

Now, what if our users want to search on more than one at a time? Like Firstname and Lastname? Now we have to build our SQL statement manually:

if len(@firstname) > 0
set @where = @where + " AND firstname LIKE '" + @firstname+ "'"

if len(@lastname) > 0
set @where = @where + " AND lastname LIKE '" + @lastname+ "'"

--uggggh

This is time consuming and doesn't allow SQL server to optimize your queries. And it just isn't fun :).

Now check this out. Using our trusty COALESCE, we can get it all in one SELECT:

SELECT * FROM Customers
WHERE
ISNULL(firstname,'') = COALESCE(@firstname,firstname, '')
AND isnull(lastname,'') = COALESCE(@lastname,lastname, '')
AND isnull(phone,'') = COALESCE(@phone,phone, '')
AND isnull(email,'') like COALESCE(@email,email, '')
AND isnull(address,'') like COALESCE(@address,address, '')

Ok, some further explanation on what's going on here. Let's pretend we only want to search on firstname, so @firstname equals “Scott”. So ISNULL(firstname,'') will equal the firstname of each row we are searching. If the row's firstname happens to be null, it will be replaced with ''. So if there were only one row in our Customers table and the customer's name was “Bob”, ISNULL(“Bob”,'') would evaluate to “Bob”.

Now COALESCE will give us the first non-null value we gave it, so it is very important to look at the order of arguments. COALESCE(@firstname,firstname, '') will return the value of @firstname if it is not null, else it will return the value of the firstname of the row if that isn't null, then last but not least it will return ''. In this case, it will return “Scott”, since we specified it earlier. If we hadn't, it would of returned 'Bob' for that one row.

So here is the kicker. Since we specified “Scott” as our @firstname, the statement evaluates as “Bob” = “Scott”, which is not true and hence will not be returned. But if we hadn't specified “Scott” and left @firstname null, then the expression would be “Bob” = “Bob” which will return true, and the row will be returned. The last case we would have to worry about is if the firstname in that row was null. Looking at our expression, if the firstname is null, ISNULL(firstname, '') would be '', and COALESCE(@firstname,firstname, '') would return either “Scott” if we were searching on it, or '' if not. So either '' = “Scott” which is false, or '' = '' which is true. Pretty Nifty!

“Bob” = “Bob” may seem weird until you look at it more simply. If you write “If 2 = 2” The expression will always return true. Same is true for SELECT statements. “SELECT * FROM Customers WHERE 2 = 2” will always return all records. And as you know, a record is returned if the where clause evaluates to true. So “x = x” will return all records.

So, to look at the whole picture, if we only set @firstname = “Scott”, all the other lines will be ignored since both sides of the equation are always the same. The last couple lines I use “like” to demonstrate that this can work for that also.

I must admit that when I first was working with COALESCE, I had to stare at the screen for a bit to get it straight in my head. But once you get it, you won't forget, and your code will shrink dramatically.

Retrieving Values from SQL Exec()

Never had a good reason to do this up until now, but if you need to get a value from a generated sql string, there are a few hoops you must jump through. Example:

declare @table 		varchar(20) 
,@username varchar(30)

--can't use double quotes w/out this :)
SET QUOTED_IDENTIFIER OFF

--set your variables
select @table = 'tblcustomers'
,@username = 'webmaster'

--create a temp table to hold your value
create table #temp (field varchar(50))

--insert the value into temp table
insert into #temp
exec ('select email from ' + @table + ' where username = "' + @username + '"')

--voila
select field from #temp

drop table #temp

Reference: Getting Creative with EXEC and EXEC()

Sql Dependencies

This is a rad sproc to find dependencies from Brett. Sometimes I come across a sproc that I *think* I can delete but you never can be too sure. This makes that process A LOT easier. I am reposting it here for my own benefit with some slight formatting differences to make it easier for me to read:

/* testing...

exec usp_depends2 ‘tblCustomers’

*/
CREATE procedure usp_depends2
@objname nvarchar(776) –the object we want to check

as

declare @objid int –the id of the object we want
,@found_some bit –flag for dependencies found
,@dbname sysname

–Make sure the @objname is local to the current database.

DECLARE @sp_depends_xref table (
reftype char(2)
, dep_name nvarchar(256)
, type char(16)
, updated char(7)
, selected char(8)
, [column] nvarchar(128)
)

select @dbname = parsename(@objname,3)

if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)
return (1)
end

–See if @objname exists.
select @objid = object_id(@objname)
if @objid is null
begin
select @dbname = db_name()
raiserror(15009,-1,-1,@objname,@dbname)
return (1)
end

–Initialize @found_some to indicate that we haven’t seen any dependencies.
select @found_some = 0

set nocount on

–Print out the particulars about the local dependencies.
if exists (select * from sysdepends where id = @objid)
begin
raiserror(15459,-1,-1)
INSERT INTO @sp_depends_xref (
refType
,dep_name
,type
,updated
,selected
,[column]
)
SELECT
‘TO’
,’name’ = (s6.name+ ‘.’ + o1.name)
,type = substring(v2.name, 5, 16)
,updated = substring(u4.name, 1, 7)
,selected = substring(w5.name, 1, 8)
,’column’ = col_name(d3.depid, d3.depnumber)
FROM
sysobjects o1
,master.dbo.spt_values v2
,sysdepends d3
,master.dbo.spt_values u4
,master.dbo.spt_values w5
,sysusers s6
WHERE
o1.id = d3.depid
and o1.xtype = substring(v2.name,1,2) collate database_default and v2.type = ‘O9T’
and u4.type = ‘B’ and u4.number = d3.resultobj
and w5.type = ‘B’ and w5.number = d3.readobj|d3.selall
and d3.id = @objid
and o1.uid = s6.uid
and deptype < 2

select @found_some = 1
end

–Now check for things that depend on the object.
if exists (select * from sysdepends where depid = @objid)
begin
raiserror(15460,-1,-1)

INSERT INTO @sp_depends_xref (
RefType
,dep_name
,type
)
SELECT DISTINCT
‘BY’
,’name’ = (s.name + ‘.’ + o.name)
,type = substring(v.name, 5, 16)
FROM
sysobjects o
,master.dbo.spt_values v
,sysdepends d
,sysusers s
WHERE
o.id = d.id
and o.xtype = substring(v.name,1,2) collate database_default and v.type = ‘O9T’
and d.depid = @objid
and o.uid = s.uid
and deptype < 2

select @found_some = 1
end

–Did we find anything in sysdepends?

if @found_some = 0
raiserror(15461,-1,-1)

SELECT
reftype
,dep_name
,type
,updated
,selected
,[column]
FROM
@sp_depends_xref

set nocount off

return (0)

[Listening to: Postmortem – Slayer (3:27)]

Count Distinct Rows in SQL Server

Question: How do you count distinct rows in a query?

1st Try: I *thought* this would work:


select distinct count(taskid) from #temp

however, it returned the count of all taskid's.

2nd Try: then I tried keeping duplicates out to begin with, so I could just do a normal count(taskid). That wasn't fool proof.

Solution: Just out of the “what-would-happen-if-I-did-this” department:

select count(distinct taskid) from #temp

Looks totaly wrong, but it worked!??! Althought I have never heard or seen this done, I am sure people will show me up by telling me how it is “all over Books Online” and such. Whatever, for now, I “invented” it.

Generate Inserts from a Stored Procedure

I have said it before, but I use this website as a storage facility for my brain. Trying to find that one useful script I used 2 years ago is always harder than it needs to.

Well, here is the latest cool script I couldn't find. It was written by Narayana Vyas Kondreddi and is immensely useful.

CREATE  PROC sp_generate_inserts
(
@table_name varchar(776), -- The table/view for which the INSERT statements will be generated using the existing data
@target_table varchar(776) = NULL, -- Use this parameter to specify a different table name into which the data will be inserted
@include_column_list bit = 1, -- Use this parameter to include/ommit column list in the generated INSERT statement
@from varchar(800) = NULL, -- Use this parameter to filter the rows based on a filter condition (using WHERE)
@include_timestamp bit = 0, -- Specify 1 for this parameter, if you want to include the TIMESTAMP/ROWVERSION column's data in the INSERT statement
@debug_mode bit = 0, -- If @debug_mode is set to 1, the SQL statements constructed by this procedure will be printed for later examination
@owner varchar(64) = NULL, -- Use this parameter if you are not the owner of the table
@ommit_images bit = 0, -- Use this parameter to generate INSERT statements by omitting the 'image' columns
@ommit_identity bit = 0, -- Use this parameter to ommit the identity columns
@top int = NULL, -- Use this parameter to generate INSERT statements only for the TOP n rows
@cols_to_include varchar(8000) = NULL, -- List of columns to be included in the INSERT statement
@cols_to_exclude varchar(8000) = NULL, -- List of columns to be excluded from the INSERT statement
@disable_constraints bit = 0, -- When 1, disables foreign key constraints and enables them after the INSERT statements
@ommit_computed_cols bit = 0 -- When 1, computed columns will not be included in the INSERT statement

)
AS
BEGIN

/***********************************************************************************************************
Procedure: sp_generate_inserts (Build 22)
(Copyright