Browse by Tags

All Tags » SQL   (RSS)

Aug
01
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 Read More...
0 Comments
Filed under
Apr
19
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@inputvarchar(100)set@input="redcowboyprettyhat"--declarationsDECLARE@iint,@lenint,@wordvarchar(100),@charvarchar(1),@newphrasevarchar(1000)--Variable/ConstantinitializationsSET@i=1SET@len=LEN(@input)SET@word=""SET@newphrase=""createtable#temp(wordvarchar(100))WHILE@i@lenbeginSET@char=SUBSTRING(@input,@i,1)IF@char=""begin--addwordtotemptableiflen(@word)>0insertinto#temp(word)select@wordSET@word=""endelseSET@word=@word+@charSET@i=@i+1endselect@newphrase=@newphrase+""+wordfrom#temporderbywordprint@newphrasedroptable#temp Read More...
0 Comments
Filed under
Mar
24
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 Read More...
5 Comments
Filed under ,
Feb
23
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: .code { word-wrap:break-word; Read More...
4 Comments
Filed under ,
Sep
10
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 Read More...
0 Comments
Filed under ,
May
06
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 Read More...
12 Comments
Filed under ,
May
06
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 Read More...
1 Comments
Filed under ,
Apr
29
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 Read More...
1 Comments
Filed under ,
Feb
06
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 Read More...
2 Comments
Filed under ,
Dec
23
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 Read More...
0 Comments
Filed under ,
Aug
17
Setting a Column to Itself in a Where Clause
Many developers don't realize how handy it can be to set a column to itself in a WHERE clause in SQL. Here is a short list of when I have used it to tidy things up: Lets say I have a sproc that lets me choose ALL employees OR only employees that are Read More...
3 Comments
Filed under ,
Jul
23
Export your Data In Sql Server Using A Stored Procedure
This is seriously one of the coolest stored procedures there is. It is called sp_generate_inserts. I did not write this. I got pissed off one day because I was tired of dealing with the pains of exporting/importing data, so I started looking around before Read More...
4 Comments
Filed under ,
Jul
20
Find All Children of Parent in Stored Procedure
Given a Parent ID, what is the best way to return all child records, their child records, ad inifitum? I needed this to find all employees beneath a given manager no matter how deep. So given a table of employees with a employeeid and managerid field, Read More...
3 Comments
Filed under ,
Jun
18
How To Debug Stored Procedures
Post inprogress. As I have two friends learning the fun of Stored Procedures, I figure I would write up the questions and how-to's they are experiencing and I am having to demo or explain. Create a dummy Stored Procedure (hereafter referred to as Read More...
1 Comments
Filed under ,
Jun
10
Sql Server Templates
I keep saying this, but I can't believe I have been programming in Sql Server for this many years, and never heard of templates. While in Query Analyzer, hit Ctrl-Shift-Ins. Choose the type of template you want to use. Replace all the code between Read More...
2 Comments
Filed under ,
More Posts Next page »