Programming in SQL Server 2005

A lot of programming can be done within Stored Procedures. Also when you are writing scripts to accomplish a complex task, it can be done more easily with programming. You can easily loose your way writing sub-queries, and you can end up compromising the actual task with something close to it but not really it.


There are a lot of things a person needs to learn in order to get the needed results and fast. And for that you need to have a little more knowledge then SELECT.

I haven't been able to find any good tutorials that would take me from the basics of programming in SQL to higher levels. There are a bulk of resources for Oracle but none that I could find for SQL. Yeah, maybe I lack adequate searching skills but whatever the reason, I have been restrained to use Oracle PL-SQL guide for learning SQL.
Now what I am doing is that I read something from Oracle Guide and then I try it out in SQL Server 2005 and when it doesn't work I search for that particular topic or syntax equivalent in SQL.

And what I am going to do here at my blog is that I'll write small posts giving the syntax for programming in SQL (something beyond the basic Transaction-SQL statements).

I don't have much to offer, but well.......

Anyone who can give me a reason for why I didn't get any decent search results is always welcome to explain it to me through comment.


Declaring Variables:

Syntax:

DECLARE @variable_name data_type

http://msdn.microsoft.com/en-us/library/ms188927(SQL.90).aspx


Declaring Cursors:
I don't know how to exactly define a cursor. (After all I am no DB Programming Guru, just in my learning phase.)

But cursors can be used when you want to select a number of rows and then iterate through each row one at a time, or manipulate them in any way.

Here's the
Sytanx:

DECLARE cursor_name CURSOR FOR sql_stmt

Example:
DECLARE my_cur CURSOR FOR SELECT * FROM Table_1

You don't have to start the cursor name with '@'.

http://msdn.microsoft.com/en-us/library/ms180169(SQL.90).aspx

In order to execute the statement and populate cursor OPEN statement is used.

OPEN:

Syntax:
OPEN cursor_name

http://msdn.microsoft.com/en-us/library/ms190500(SQL.90).aspx

After the rows have been retrieved and stored in cursor, they can be worked on by using the FETCH statement.

FETCH:

Syntax:
FETCH position FROM cursor_name

where
position can be any of the following:
NEXT
PRIOR
FIRST
LAST

http://msdn.microsoft.com/en-us/library/ms180152(SQL.90).aspx


Note:
I know you may not find this information very useful. It's all just basic information, and my resource is too the internet, where anyone could get it easily. But as I said I too am in learning stages. I'll try to update my blog with the new things I learn.
And these are just an overview of possibilities. You get to know what things you can do, and then you can refer to the links I have provided to learn more about them.
This post is not in any way a tutorial, just a baby-tutorial, to provide you with guidelines.

References:
I am using the following book for Oracle Sytax:
PL/SQL
User’s Guide and Reference
Release 2 (9.2)
March 2002
Part No. A96624-01

The following links were referred to for SQL Syntax (Some of these have been stated above as well)

• http://msdn.microsoft.com/en-us/library/ms188927(SQL.90).aspx
• http://msdn.microsoft.com/en-us/library/ms180169(SQL.90).aspx
  
• http://msdn.microsoft.com/en-us/library/ms190500(SQL.90).aspx
  
• http://msdn.microsoft.com/en-us/library/ms180152(SQL.90).aspx