T-SQL Syntax in Stored Procedure Question

SQL Apprentice Question
I haven't used MS-SQL for many years and I'm now trying to port a
Firebird database to MS-SQL 2000. Of course the syntax for store
procedures and triggers is significantly different. I don't want to
get into a here's_the_way_we_used_to_do_it_back_home pissing match,
but here's an example of an very simple SP in Firebird/Interbase:


create procedure spAintThisCool(iCoolID BigInt)
AS
declare variable sField1 VarChar(50);
declare variable sField2 VarChar(50);
declare variable sField2 Text;
Begin
for Select field1, field2, field3
from tblCool
where (CoolID = :iCoolID)
into :sField1, :sField2, sField2
do
Begin
// do some cool stuff with the variables
End
End


The "for" key word would allow us to iterate through the table taking
the values of each row and storing them into the variables declared
using the "into" key word. We could then use the variables in what
ever calculations were needed.


How would this be done in T-SQL?


Also, one of the fields I need to read is a BLOB field with text
content which I believe is called a TEXT field in MS-SQL. Is there
anything special I need to do the read/write fields of type TEXT or do
I just treat it like a VarChar() field?


Finally, the SP examples in the Northwind database are quite simple.
Is there a better source of examples with perhaps some more complex
T-SQL SPs?



Celko Answers
A few syntax things for later:

1) T-SQL declares local variables with a prefix @ at the start of a
BEGIN-END block. No VARIABLE keyword is used.


2) T-SQL uses SELECT ..INTO to load a table, not as a singleton SELECT
as per Standard SQL.


3) Quit putting datatype prefixes on data element names and "sp-" on
procedures. Nothing to do with T-SQL versus some other 4GL; this is
straight ISO-11179 standards and good programming. People will think
you are an OO hillbilly :)


4) There is no FOR loop. You can do it with **uggggh** if you want to
slow things down by at least an order of magnitude.


5) Comments are C-style /* */ pairs or ANSI -- at the start of a line.


6) TEXT is a common name, but everyone has a different version in their
proprietary code; avoid it unless you **really* need it.


7) T-SQL is a very simple one-pass compiler that does not optimize like
other 4GLs. Keep your code as short and simple as you can. The old
"rule of thumb" is no more than one page (50 lines) per procedure.


8) The first execution determines the plan used. Recompiles can be
important and so can modularization. Kimberly Tripp has a good
presentation on SP in T-SQL; see if you can find it.


9) BIGINT is not a cool identifier. No validation. No verification.
Surely you look for industry standard first and when you have to design
an identifier yourself (< 15% of the time), you use a check digit, an
appropriate encoding, etc. Yeah, yeah, I know it is pseudo-code, but
it is (unfortunately) worth mentioning.


10) If you showed us the "cool stuff" someone here can probably write
it with a single SQL statement instead of a cursor.


10) Trapping errors in T-SQL is done with a global flag that has to be
caught then processed. Look up some examples. It is not the SQL/PSM
or the try-catch model.


Good luck on your conversion!