VARIABLE DECLARATION

Database developers usually have one thing in common: They do not like

to spend their time typing repetitive code. This can be eased somewhat

by utilizing variable declarations. By making effective use of the

system tables, you can quickly generate variable names on the metadata

of a table.

Assume you are writing a stored procedure that needs one variable

defined for each column name. If this table is large, meaning it has

many columns, you will find that you may spend a good deal of your time

typing out variable names.

Use the following statement to get a head start on your declarations

for the pubs..authors table. We perform this with the string

concatenation operator, the plus sign (+).

SELECT '@' + b.name + ' ' + c.name + '(' + CAST(b.length AS

VARCHAR(5))+ '), ' FROM sysobjects a, syscolumns b, systypes c

WHERE a.id =b.id

AND a.name = 'authors'

AND b.xtype = c.xtype

This will output something like the following: (abbreviated for

clarity)

@contract bit(1),

@phone char(12),

@state char(2),

@zip char(5),

.

.

.

With a little cleaning up and the addition of the DECLARE statement,

you now have a valid set of variables that are ready to cut and paste

into your stored procedure definition.

DECLARE @contract bit,

@phone char(12),

@state char(2),

@zip char(5)