Archive for the ‘SQL For Programmers’ Category
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 For Programmers – New Question
I got this question last week that I thought would be worth discussing on the blog.
I would like to create a table that lists contributions by date, amount, and designation. In some cases one gift was split two (or more) ways. For example, a contribution of $1000 was given, and $500 went to the scholarship fund and $500 to athletics. The gift date would be the same for both gifts, but they must be recorded separately in order to split the gift. In my new table, I want to list them in rows like this:
date| amount| designation1, designation2, designation3, etc.Would it be best to use a cursor or a stored procedure. I’ve never done anything like this before, so I’m not sure where to begin.
SQL – Transactions
Ruth ask,
How do I do transaction tracking within a stored procedure?
Hopefully, if you’ve been programming for any length of time against databases, you know that if you are updating multiple records in a database, you need to supply some sort of transaction tracking so that it either all works or all fails.
You do know that, right?
Well, maybe we should cover that in a future post.
But today, what happens if you have a stored procedure that updates multiple records in the database? I can’t depend on the programmer to implement transaction tracking because all he sees is one call. How is he supposed to know that it’s updating multiple records and should turn on transaction tracking in his code?
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?”
