Databases Reference
In-Depth Information
scripts as comments as a way of providing in-line documentation. Comments
start with /* and end with */. Everything in between these, even if it runs on
for several lines, is treated as a comment. You can also use -- (a double hyphen)
to create a single line comment.
Let's add some comments to an earlier script example so you can see how
this is done.
/* This batch gives everyone the
same default percentage
as their commission percentage. This is stored in the
COMMPERCT column*/
DECLARE @avgpct REAL
This declares a variable.
/*The next statement sets the variable value*/
SELECT @avgpct = (
SELECT AVG(COMMPERCT) FROM SALESPERSON)
—This does the real work
UPDATE SALESPERSON SET COMMPERCT = @avgpct
FOR EXAMPLE
Using Scripts
Here's a common situation. You have several procedures that have to run at
the end of each month to do periodic cleanup and to prepare data for trans-
fer to a decision support database. You want to ensure that it is run the
same way each time, but you also want to minimize the time and effort
required. This is a situation made for scripting.
You can include all of the procedures that you want to run as batches
inside a script. You might want to use multiple batches. Group procedures
that are dependent on each other to run in the same batch so that if one
fails, the other doesn't try to run. Also, if one part of the process fails because
of an error, it doesn't prevent procedures that aren't dependent on that oper-
ation from running. You can use control language if you have any situations
where processing decisions have to be made based on current conditions.
For example, the way that you process records for transfer could depend on
the total number of rows in a table. If they exceed a set threshold level, you
could segment processing to minimize the impact on other activities.
SQL Server also has the ability to create scripts based on any and all
objects in a database. You can automatically generate scripts that can be used
to create duplicate objects in another database or at another location, drop
objects, and (in some cases) modify objects. You can even generate table and
view scripts for running standardized INSERT, UDPATE, and DELETE state-
ments. That way, you let SQL Server do a big part of the work for you and
you then modify the scripts generated to meet your particular needs.
