Scott Elkin

Category: SQL

  • 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…

  • 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…

  • 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…

  • 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 =…

  • 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…

  • 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…

  • 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 variablesselect @table = 'tblcustomers' ,@username = 'webmaster'–create…

  • 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…

  • 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…

  • 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…