Archives of the TeradataForum

Message Posted: Mon, 14 Nov 2005 @ 15:27:11 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Sure. Using cursors and loops in procedures is much slower than set-oriented SQL, so if you can use plain SQL to get the job done, you should. But consider the following situations.

1. You want to generate the "next" number for a unique identifier and don't want to use an IDENTITY column. You must therefore lock the table (raising an error if the lock fails), generate the next number, return it, and unlock the table. I have used this technique in a procedure.

2. You have a long BTEQ script that executes SQL statements A and B under some conditions but statements C and D under other conditions (probably determined by .if errorcode... or .if activitycount...). The script could be converted to a procedure.

3. You want to generate dynamic SQL and run it on the fly. Procedures will do this, although the access rights for doing it require some attention, and in my opinion there are better ways (e.g. Perl).

4. You have a problem that demands iterative processing. There probably aren't many of these, but one never knows, do one?

I confess that I have trouble coming up with a lot of uses for procedures. Perhaps others have more to add.

	<Prev	Next>		<<First	<Prev	Next>	Last>>