Creating and Managing SQL Scripts - Microsoft Business Intelligence Tools for Excel Analysts - page 203

Without a Try and Catch statement, SQL Server keeps executing any additional steps in
your script until all of them are completed.

Caution

Listing 10-6: Try and Catch for Error Handling
BEGIN TRY
--Raise error if you are trying to trap a condition or skip this line if you
are not
RAISERROR('Customer Error',16,1)
END TRY

/****** Error Handling ******/
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE()

RAISERROR
(
@ErrorMessage,
@ErrorSeverity,
@ErrorState
)
END CATCH

Beyond Try and Catch, always design your scripts to be restartable to avoid corrupting your system in
case it was accidentally run more than once or to allow repopulation of incremental data in case of
source system data corruption.

Creating and altering stored procedures
Stored procedures are SQL scripts that are stored in SQL Server. The benefit of using them for loading
your analytics system is mostly for organization and ease of maintenance. Once built, you can call
stored procedures from other SQL Server components, such as SQL Server Agent Jobs (for scheduling
purposes) or by other procedures or applications. To create a stored procedure, follow these steps:

1. Open SQL Server Management Studio and expand the Programmability folder in your database.

2. Right-click the Stored Procedures subfolder and choose New Stored Procedure.

A Query window opens with a default stored procedure structure, as shown in Figure 10-9.

3. Insert your SQL scripts in between the Begin and End blocks and change the information in
between <> according to your needs.

4. When done, click Execute to create the stored procedure.