Posts Tagged ‘tsql’
SQL SELECT CASE Instead of IIF
October 20th, 2009 | 
Author: 
I’ve had to do this a couple of times and I just realized I haven’t written about it anywhere.
If you need to SELECT a field from a row that returns different content based on the content of the field, you’d think, based on previous programming experience, that your code would look something like this.
Posted in 
Tags: 
SQL – Filtering WHERE condition on two rows
I received the following question a couple of days ago from a programmer using the “Ask a Question” form which you can access from the menu of this blog:
Given the following table:
| Name | Language |
| Nikhil | Hindi |
| Nikhil | English |
| Kisu | Hindi |
| Kisu | English |
| Rakesh | Hindi |
| Kousik | Bangali |
How do I select names of persons who know both Hindi and English?
In this table, that query should return Nikhil and Kisu.
Random in SQL – SQL For Programmers
Last week I had an interesting assignment: Given a specific record, randomly retrieve three related records from the database.
Naturally, as a programmer, I started looking for the SQL random function. Which I found.
But even as I was searching for how to use that function, I was thinking to myself, “How am I going to structure things in such a way so as to randomly select the records once I have the function? It isn’t like I can retrieve the records into an array and then select them out. Am I going to have to retrieve them into a temp table?”
SQL CURSOR Performance – SQL For Programmers
Last week I showed how to use the SQL CURSOR to loop through records in a database. In that article I mentioned that you want to avoid using the CURSOR if you can because it has performance problems.
The article was posted on DZone where a self-proclaimed SQL guru picked it up and left a comment basically stating an alternate way of looping through records that would do the same thing but didn’t require the CURSOR. What he suggested was the common practice of doing something like this:
SQL CURSOR – SQL For Programmers
One of the things it took me a while to figure out was how to get a stored procedure to loop through data in a table and pull out specific data I needed.
There are actually two parts to this question. First, you should always try to find some other way of doing whatever it is you think you need to do by processing one row at a time. SQL is a “Set-Based” language and as such it expects you to work with the whole set. That’s what it was designed to do best and that’s why it has functions such as MAX, MIN, SUM, AVG, etc. Many of these functions in combination with a good WHERE clause will get you the information you need.
However, there are times when you’ll need to process the information one record at a time, and this is what the CURSOR was created for.
