SQL Server › T-SQL Fundamentals & Syntax

How would you write a T-SQL script that combines variables, control flow, and error handling together to perform a robust, multi step administrative task?

Advanced

Answer

You would declare variables to track state throughout the script, use control flow statements like WHILE loops or IF conditions to implement the required logic, wrap risky operations in a TRY CATCH block to gracefully handle any errors, and use PRINT statements or a logging table to provide visibility into the script's progress and outcome.

DECLARE @TableName VARCHAR(100), @RowCount INT;
BEGIN TRY
  SELECT @RowCount = COUNT(*) FROM Orders WHERE Status = 'Pending';
  IF @RowCount > 0
  BEGIN
    PRINT CAST(@RowCount AS VARCHAR) + ' pending orders found';
  END
END TRY
BEGIN CATCH
  PRINT 'Error: ' + ERROR_MESSAGE();
END CATCH;

Real-world example A database administrator writes a comprehensive maintenance script combining variables, conditional logic, and proper error handling to safely check and report on pending orders across the system every night.

Common follow-ups: What logging strategy works well for a long running administrative script like this?;How do you test a complex T-SQL script safely before running it in production?

Error Handling with TRY CATCH;SQL Server Agent & Job Scheduling