tsql

Full Reference: See advanced.md for multi-statement TVFs, custom error messages, INSTEAD OF triggers, transaction isolation levels, cursors, dynamic SQL, and recursive CTEs.

Safety Notice

This listing is imported from skills.sh public index metadata. Review upstream SKILL.md and repository scripts before running.

Copy this and send it to your AI assistant to learn

Install skill "tsql" with this command: npx skills add claude-dev-suite/claude-dev-suite/claude-dev-suite-claude-dev-suite-tsql

T-SQL Core Knowledge

Full Reference: See advanced.md for multi-statement TVFs, custom error messages, INSTEAD OF triggers, transaction isolation levels, cursors, dynamic SQL, and recursive CTEs.

Deep Knowledge: Use mcp__documentation__fetch_docs with technology: sqlserver for comprehensive documentation.

Basic Structure

-- Anonymous block BEGIN DECLARE @count INT = 0; SET @count = @count + 1; PRINT 'Count: ' + CAST(@count AS VARCHAR); END; GO

Variables

DECLARE @name VARCHAR(100) = 'John'; DECLARE @age INT; DECLARE @salary DECIMAL(10,2), @bonus DECIMAL(10,2);

SET @age = 25; SELECT @salary = salary FROM employees WHERE id = 1;

-- Multiple assignments SELECT @salary = salary, @bonus = bonus FROM employees WHERE id = 1;

-- Table variable DECLARE @employees TABLE ( id INT, name VARCHAR(100), salary DECIMAL(10,2) );

INSERT INTO @employees SELECT id, name, salary FROM employees;

Stored Procedures

Basic Procedure

CREATE OR ALTER PROCEDURE usp_GetEmployee @EmployeeId INT AS BEGIN SET NOCOUNT ON;

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE employee_id = @EmployeeId;

END; GO

-- Execute EXEC usp_GetEmployee @EmployeeId = 100; -- or EXEC usp_GetEmployee 100;

Procedure with OUTPUT Parameters

CREATE OR ALTER PROCEDURE usp_GetEmployeeStats @DeptId INT, @EmployeeCount INT OUTPUT, @TotalSalary DECIMAL(15,2) OUTPUT, @AvgSalary DECIMAL(15,2) OUTPUT AS BEGIN SET NOCOUNT ON;

SELECT
    @EmployeeCount = COUNT(*),
    @TotalSalary = SUM(salary),
    @AvgSalary = AVG(salary)
FROM employees
WHERE department_id = @DeptId;

END; GO

-- Call with OUTPUT DECLARE @Count INT, @Total DECIMAL(15,2), @Avg DECIMAL(15,2); EXEC usp_GetEmployeeStats @DeptId = 10, @EmployeeCount = @Count OUTPUT, @TotalSalary = @Total OUTPUT, @AvgSalary = @Avg OUTPUT;

PRINT 'Count: ' + CAST(@Count AS VARCHAR);

Procedure with Return Value

CREATE OR ALTER PROCEDURE usp_ValidateEmployee @EmployeeId INT AS BEGIN SET NOCOUNT ON;

IF NOT EXISTS (SELECT 1 FROM employees WHERE employee_id = @EmployeeId)
    RETURN -1;  -- Not found

IF EXISTS (SELECT 1 FROM employees WHERE employee_id = @EmployeeId AND status = 'INACTIVE')
    RETURN -2;  -- Inactive

RETURN 0;  -- Success

END; GO

-- Check return value DECLARE @result INT; EXEC @result = usp_ValidateEmployee @EmployeeId = 100;

IF @result = 0 PRINT 'Valid'; ELSE IF @result = -1 PRINT 'Employee not found';

Functions

Scalar Function

CREATE OR ALTER FUNCTION dbo.fn_CalculateBonus( @Salary DECIMAL(10,2), @YearsOfService INT ) RETURNS DECIMAL(10,2) AS BEGIN DECLARE @Bonus DECIMAL(10,2);

SET @Bonus = CASE
    WHEN @YearsOfService >= 10 THEN @Salary * 0.15
    WHEN @YearsOfService >= 5 THEN @Salary * 0.10
    ELSE @Salary * 0.05
END;

RETURN @Bonus;

END; GO

-- Usage SELECT employee_id, salary, dbo.fn_CalculateBonus(salary, years_of_service) AS bonus FROM employees;

Inline Table-Valued Function

CREATE OR ALTER FUNCTION dbo.fn_GetDeptEmployees( @DeptId INT ) RETURNS TABLE AS RETURN ( SELECT employee_id, first_name, last_name, salary FROM employees WHERE department_id = @DeptId ); GO

-- Usage SELECT * FROM dbo.fn_GetDeptEmployees(10);

Control Flow

IF...ELSE

DECLARE @status VARCHAR(20);

IF @status = 'ACTIVE' BEGIN PRINT 'User is active'; -- Multiple statements in BEGIN...END END ELSE IF @status = 'PENDING' PRINT 'User is pending'; -- Single statement, no BEGIN needed ELSE BEGIN PRINT 'User is inactive'; END;

CASE Expression

SELECT employee_id, salary, CASE WHEN salary >= 100000 THEN 'Executive' WHEN salary >= 50000 THEN 'Senior' WHEN salary >= 30000 THEN 'Mid' ELSE 'Junior' END AS level FROM employees;

-- Simple CASE SELECT employee_id, CASE status WHEN 'A' THEN 'Active' WHEN 'I' THEN 'Inactive' ELSE 'Unknown' END AS status_name FROM employees;

WHILE Loop

DECLARE @counter INT = 1;

WHILE @counter <= 10 BEGIN PRINT 'Counter: ' + CAST(@counter AS VARCHAR); SET @counter = @counter + 1;

IF @counter = 5
    CONTINUE;  -- Skip to next iteration

IF @counter = 8
    BREAK;  -- Exit loop

END;

Error Handling

TRY...CATCH

BEGIN TRY BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT TRANSACTION;

END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

-- Error information
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
DECLARE @ErrorState INT = ERROR_STATE();

-- Re-throw or log
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);

END CATCH;

Error Functions

Function Description

ERROR_NUMBER()

Error number

ERROR_MESSAGE()

Error message

ERROR_SEVERITY()

Error severity (0-25)

ERROR_STATE()

Error state

ERROR_LINE()

Line number where error occurred

ERROR_PROCEDURE()

Stored procedure name

THROW vs RAISERROR

-- THROW (SQL Server 2012+, preferred) THROW 50001, 'Custom error message', 1;

-- THROW without parameters re-throws current error BEGIN CATCH INSERT INTO error_log (message, error_time) VALUES (ERROR_MESSAGE(), GETDATE());

THROW;  -- Re-throw original error

END CATCH;

-- RAISERROR (legacy) RAISERROR('Error: %s', 16, 1, @ErrorMessage);

Triggers

DML Trigger

CREATE OR ALTER TRIGGER tr_employees_audit ON employees AFTER INSERT, UPDATE, DELETE AS BEGIN SET NOCOUNT ON;

-- Handle INSERT
IF EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
BEGIN
    INSERT INTO employees_audit (action, employee_id, new_salary, changed_by, changed_at)
    SELECT 'INSERT', employee_id, salary, SYSTEM_USER, GETDATE()
    FROM inserted;
END

-- Handle UPDATE
IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
BEGIN
    INSERT INTO employees_audit (action, employee_id, old_salary, new_salary, changed_by, changed_at)
    SELECT 'UPDATE', i.employee_id, d.salary, i.salary, SYSTEM_USER, GETDATE()
    FROM inserted i
    INNER JOIN deleted d ON i.employee_id = d.employee_id;
END

-- Handle DELETE
IF NOT EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
BEGIN
    INSERT INTO employees_audit (action, employee_id, old_salary, changed_by, changed_at)
    SELECT 'DELETE', employee_id, salary, SYSTEM_USER, GETDATE()
    FROM deleted;
END

END; GO

Transactions

BEGIN TRANSACTION; -- or BEGIN TRAN;

SAVE TRANSACTION SavePoint1;

-- Rollback to savepoint ROLLBACK TRANSACTION SavePoint1;

COMMIT TRANSACTION; -- or COMMIT;

-- Check transaction count SELECT @@TRANCOUNT;

-- Named transaction BEGIN TRANSACTION MyTransaction; COMMIT TRANSACTION MyTransaction;

Common Table Expressions (CTE)

;WITH dept_stats AS ( SELECT department_id, COUNT(*) AS emp_count, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ) SELECT d.department_name, ds.emp_count, ds.avg_salary FROM departments d INNER JOIN dept_stats ds ON d.department_id = ds.department_id;

Best Practices

DO

  • Use SET NOCOUNT ON in procedures

  • Use TRY...CATCH for error handling

  • Use sp_executesql for dynamic SQL

  • Use FAST_FORWARD cursors when possible

  • Use table-valued parameters for batch operations

  • Always qualify object names with schema

DON'T

  • Use SELECT * in production code

  • Build dynamic SQL with string concatenation

  • Use cursors when set-based operations work

  • Ignore error handling

  • Use deprecated features (GROUP BY ALL, etc.)

When NOT to Use This Skill

  • Basic SQL Server SQL - Use sqlserver skill for data types, indexes, temporal tables

  • PL/pgSQL (PostgreSQL) - Use plpgsql skill for PostgreSQL procedures

  • PL/SQL (Oracle) - Use plsql skill for Oracle procedures

  • Basic SQL - Use sql-fundamentals for ANSI SQL basics

Anti-Patterns

Anti-Pattern Problem Solution

Not using TRY...CATCH Silent errors Add error handling blocks

Using RAISERROR Deprecated pattern Use THROW (SQL 2012+)

Missing SET NOCOUNT ON Performance overhead Add to all procedures

String concatenation for dynamic SQL SQL injection Use sp_executesql with parameters

Using SELECT without ORDER BY for TOP Non-deterministic results Always specify ORDER BY

Cursors when set-based works Poor performance Rewrite as set operations

Quick Troubleshooting

Problem Diagnostic Fix

Transaction uncommitted SELECT @@TRANCOUNT

Add COMMIT or ROLLBACK

Procedure slow Execution plan in SSMS Add indexes, rewrite queries

Error swallowed Check CATCH block Ensure THROW or logging

Deadlock victim Extended Events Consistent access order

Parameter sniffing Compare plans Use OPTION (RECOMPILE) or local variables

Reference Documentation

  • Procedures

  • Functions

  • Error Handling

Source Transparency

This detail page is rendered from real SKILL.md content. Trust labels are metadata-based hints, not a safety guarantee.

Open in GitHubOpen in ClawHub

Related Skills

Related by shared tags or category signals.

Coding

cron-scheduling

No summary provided by upstream source.

Repository SourceNeeds Review
-35
claude-dev-suite
Coding

token-optimization

No summary provided by upstream source.

Repository SourceNeeds Review
-27
claude-dev-suite
Coding

react-19

No summary provided by upstream source.

Repository SourceNeeds Review
-18
claude-dev-suite