.NET Answers

Archive for the ‘SQL For Programmers’ Category

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:

Read the rest of this entry »

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.

Read the rest of this entry »

You may think that you don’t need a temporary table.  But if you’ve ever retrieved data from your database or retrieved data from a table and put it in a list of some sort simply to process it further with your code, you need to learn about temporary tables.

I’ve also needed to use Temporary Tables to simulate arrays in my stored procedures.  You can pass in a comma delimited list and then have your stored procedure process the list into a temporary table, making the information easier to evaluate.

Read the rest of this entry »

The IF statement we looked at on Tuesday was pretty tame compared to the WHILE construct.

Actually, the main thing you need to keep in mind is that WHILE is all you have.  There is no such thing as a FOR loop or a DO WHILE loop.  So, you have to force WHILE to do those for you.

The basic syntax of WHILE looks like this:

Read the rest of this entry »

OK, all you programmer types.  Here’s where SQL starts getting interesting.  You have the basic concepts of declaring variables, setting up stored procedures, and doing a basic insert, update or delete statement.  And if we stopped here, you’d probably be able to do 80% of the work you need to do.

You’d also start to wonder why you should even bother.

So now we tackle the basics of conditional programming in SQL.

Read the rest of this entry »