Archive for the ‘SQL For Programmers’ Category
[Don't start the flames until you read the entire post . ]
While we could go on with the various syntax elements in a SELECT statement, I doubt that it would be as helpful as the items we’ve already covered. Most of what I use on a day to day basis is some combination of what I’ve shown already.
Where SQL really starts getting fun though, is with stored procedures.
But why use Stored Procedures at all?
Republished by Blog Post Promoter
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.
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.
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?
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:
How do I select names of persons who know both Hindi and English?
In this table, that query should return Nikhil and Kisu.