tsql — Skillopedia

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 with technology: for comprehensive documentation. Basic Structure Variables Stored Procedures Basic Procedure Procedure with OUTPUT Parameters Procedure with Return Value Functions Scalar Function Inline Table-Valued Function Control Flow IF...ELSE CASE Expression WHILE Loop Error Handling TRY...CATCH Error Functions | Function | Description | |----------|-------------| | | Error…

\n)\nRETURNS NVARCHAR(50)\nAS\nBEGIN\n RETURN @CurrencySymbol + FORMAT(@Amount, 'N2');\nEND;\nGO\n```\n\n### Validation\n\n```sql\nCREATE FUNCTION dbo.fn_IsValidEmail(\n @Email NVARCHAR(255)\n)\nRETURNS BIT\nAS\nBEGIN\n IF @Email LIKE '%_@_%.__%'\n AND @Email NOT LIKE '%[^a-zA-Z0-9.@_-]%'\n RETURN 1;\n RETURN 0;\nEND;\nGO\n```\n\n### Split String (Pre-2016)\n\n```sql\nCREATE FUNCTION dbo.fn_SplitString(\n @String NVARCHAR(MAX),\n @Delimiter NVARCHAR(10)\n)\nRETURNS @Result TABLE (value NVARCHAR(MAX))\nAS\nBEGIN\n DECLARE @start INT = 1, @end INT;\n\n WHILE @start \u003c= LEN(@String) + 1\n BEGIN\n SET @end = CHARINDEX(@Delimiter, @String + @Delimiter, @start);\n INSERT INTO @Result VALUES (SUBSTRING(@String, @start, @end - @start));\n SET @start = @end + LEN(@Delimiter);\n END;\n\n RETURN;\nEND;\nGO\n\n-- SQL Server 2016+ use STRING_SPLIT instead\nSELECT value FROM STRING_SPLIT('a,b,c', ',');\n```\n\n### Lookup Function\n\n```sql\nCREATE FUNCTION dbo.fn_GetDepartmentName(\n @DeptId INT\n)\nRETURNS NVARCHAR(100)\nAS\nBEGIN\n DECLARE @name NVARCHAR(100);\n SELECT @name = department_name FROM departments WHERE department_id = @DeptId;\n RETURN ISNULL(@name, 'Unknown');\nEND;\nGO\n```\n\n## iTVF vs mTVF Performance\n\n```sql\n-- iTVF (better performance - optimizer can inline)\nCREATE FUNCTION dbo.fn_GetOrders_Inline(@CustomerId INT)\nRETURNS TABLE AS RETURN (\n SELECT order_id, total, order_date\n FROM orders WHERE customer_id = @CustomerId\n);\n\n-- mTVF (worse performance - optimizer can't see inside)\nCREATE FUNCTION dbo.fn_GetOrders_Multi(@CustomerId INT)\nRETURNS @Result TABLE (order_id INT, total DECIMAL, order_date DATE)\nAS BEGIN\n INSERT INTO @Result\n SELECT order_id, total, order_date\n FROM orders WHERE customer_id = @CustomerId;\n RETURN;\nEND;\n\n-- Prefer iTVF when possible!\n```\n\n## Function Restrictions\n\nFunctions CANNOT:\n- Modify database state (INSERT, UPDATE, DELETE on permanent tables)\n- Use PRINT, RAISERROR\n- Call stored procedures\n- Use dynamic SQL\n- Create/alter database objects\n- Use transactions\n\n## Metadata\n\n```sql\n-- View function definition\nSELECT OBJECT_DEFINITION(OBJECT_ID('dbo.fn_MyFunction'));\n\n-- View all functions\nSELECT name, type_desc, create_date\nFROM sys.objects\nWHERE type IN ('FN', 'IF', 'TF') -- Scalar, Inline TVF, Multi-statement TVF\nAND schema_id = SCHEMA_ID('dbo');\n\n-- Check if deterministic\nSELECT OBJECTPROPERTYEX(OBJECT_ID('dbo.fn_MyFunction'), 'IsDeterministic');\n```\n\n## Drop Function\n\n```sql\nDROP FUNCTION IF EXISTS dbo.fn_MyFunction;\n```\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":6541,"content_sha256":"01a9184fd0aec9dfbe9afb978c6fc8d15f1338c7e96ec7fcf65a6a592d716e8b"},{"filename":"quick-ref/procedures.md","content":"# T-SQL Procedures Quick Reference\n\n## Basic Syntax\n\n```sql\nCREATE [OR ALTER] PROCEDURE [schema.]procedure_name\n @param1 datatype [= default] [OUTPUT | OUT],\n @param2 datatype [= default] [OUTPUT | OUT]\nAS\nBEGIN\n SET NOCOUNT ON;\n -- Procedure body\nEND;\nGO\n```\n\n## Parameter Types\n\n```sql\nCREATE PROCEDURE usp_Example\n @InputParam INT, -- Input (default)\n @OptionalParam INT = 10, -- With default value\n @OutputParam INT OUTPUT, -- Output parameter\n @InOutParam INT OUTPUT -- Can be both input and output\nAS\nBEGIN\n SET NOCOUNT ON;\n\n SET @OutputParam = @InputParam * 2;\n SET @InOutParam = @InOutParam + 1;\nEND;\nGO\n\n-- Calling\nDECLARE @out INT, @inout INT = 5;\nEXEC usp_Example\n @InputParam = 10,\n @OutputParam = @out OUTPUT,\n @InOutParam = @inout OUTPUT;\n\nSELECT @out AS OutputValue, @inout AS InOutValue;\n```\n\n## Table-Valued Parameters\n\n```sql\n-- Create type first\nCREATE TYPE dbo.EmployeeTableType AS TABLE (\n EmployeeId INT,\n Name NVARCHAR(100),\n Salary DECIMAL(10,2)\n);\nGO\n\n-- Procedure using TVP\nCREATE PROCEDURE usp_InsertEmployees\n @Employees dbo.EmployeeTableType READONLY\nAS\nBEGIN\n SET NOCOUNT ON;\n\n INSERT INTO employees (employee_id, name, salary)\n SELECT EmployeeId, Name, Salary FROM @Employees;\nEND;\nGO\n\n-- Calling with TVP\nDECLARE @emps dbo.EmployeeTableType;\nINSERT INTO @emps VALUES (1, 'John', 50000), (2, 'Jane', 60000);\nEXEC usp_InsertEmployees @Employees = @emps;\n```\n\n## Return Values\n\n```sql\nCREATE PROCEDURE usp_ProcessOrder\n @OrderId INT\nAS\nBEGIN\n SET NOCOUNT ON;\n\n IF NOT EXISTS (SELECT 1 FROM orders WHERE order_id = @OrderId)\n RETURN -1; -- Not found\n\n IF EXISTS (SELECT 1 FROM orders WHERE order_id = @OrderId AND status = 'PROCESSED')\n RETURN -2; -- Already processed\n\n UPDATE orders SET status = 'PROCESSED' WHERE order_id = @OrderId;\n RETURN 0; -- Success\nEND;\nGO\n\n-- Check return value\nDECLARE @result INT;\nEXEC @result = usp_ProcessOrder @OrderId = 100;\n\nIF @result = 0 PRINT 'Success';\nELSE IF @result = -1 PRINT 'Order not found';\nELSE IF @result = -2 PRINT 'Already processed';\n```\n\n## Temporary Procedures\n\n```sql\n-- Local temp procedure (current session only)\nCREATE PROCEDURE #usp_TempProc\nAS\nBEGIN\n SELECT 'Temporary procedure';\nEND;\nGO\n\n-- Global temp procedure (all sessions)\nCREATE PROCEDURE ##usp_GlobalTempProc\nAS\nBEGIN\n SELECT 'Global temporary procedure';\nEND;\nGO\n```\n\n## Procedure with Transactions\n\n```sql\nCREATE PROCEDURE usp_TransferFunds\n @FromAccount INT,\n @ToAccount INT,\n @Amount DECIMAL(10,2)\nAS\nBEGIN\n SET NOCOUNT ON;\n SET XACT_ABORT ON; -- Auto-rollback on error\n\n BEGIN TRY\n BEGIN TRANSACTION;\n\n -- Deduct from source\n UPDATE accounts SET balance = balance - @Amount\n WHERE account_id = @FromAccount;\n\n IF @@ROWCOUNT = 0\n THROW 50001, 'Source account not found', 1;\n\n -- Add to destination\n UPDATE accounts SET balance = balance + @Amount\n WHERE account_id = @ToAccount;\n\n IF @@ROWCOUNT = 0\n THROW 50002, 'Destination account not found', 1;\n\n COMMIT TRANSACTION;\n END TRY\n BEGIN CATCH\n IF @@TRANCOUNT > 0\n ROLLBACK TRANSACTION;\n\n THROW; -- Re-throw error\n END CATCH;\nEND;\nGO\n```\n\n## Procedure with Result Sets\n\n```sql\nCREATE PROCEDURE usp_GetDashboardData\n @UserId INT\nAS\nBEGIN\n SET NOCOUNT ON;\n\n -- Result set 1: User info\n SELECT user_id, username, email FROM users WHERE user_id = @UserId;\n\n -- Result set 2: Recent orders\n SELECT TOP 10 order_id, total, order_date\n FROM orders WHERE user_id = @UserId ORDER BY order_date DESC;\n\n -- Result set 3: Notifications\n SELECT notification_id, message, created_at\n FROM notifications WHERE user_id = @UserId AND is_read = 0;\nEND;\nGO\n```\n\n## WITH RECOMPILE\n\n```sql\n-- Recompile every execution (for varying parameters)\nCREATE PROCEDURE usp_SearchProducts\n @Category NVARCHAR(50) = NULL,\n @MinPrice DECIMAL(10,2) = NULL,\n @MaxPrice DECIMAL(10,2) = NULL\nWITH RECOMPILE\nAS\nBEGIN\n SET NOCOUNT ON;\n\n SELECT product_id, name, price\n FROM products\n WHERE (@Category IS NULL OR category = @Category)\n AND (@MinPrice IS NULL OR price >= @MinPrice)\n AND (@MaxPrice IS NULL OR price \u003c= @MaxPrice);\nEND;\nGO\n\n-- Or recompile specific execution\nEXEC usp_SearchProducts @Category = 'Electronics' WITH RECOMPILE;\n```\n\n## EXECUTE AS\n\n```sql\n-- Execute with different security context\nCREATE PROCEDURE usp_AdminTask\nWITH EXECUTE AS OWNER -- or 'dbo', 'SELF', 'CALLER', 'user_name'\nAS\nBEGIN\n SET NOCOUNT ON;\n -- Runs with owner's permissions\n DELETE FROM audit_log WHERE log_date \u003c DATEADD(DAY, -90, GETDATE());\nEND;\nGO\n```\n\n## Common Patterns\n\n### Batch Processing\n\n```sql\nCREATE PROCEDURE usp_BatchProcess\n @BatchSize INT = 1000\nAS\nBEGIN\n SET NOCOUNT ON;\n\n DECLARE @RowsAffected INT = 1;\n\n WHILE @RowsAffected > 0\n BEGIN\n UPDATE TOP (@BatchSize) orders\n SET processed = 1\n WHERE processed = 0;\n\n SET @RowsAffected = @@ROWCOUNT;\n\n -- Optional: Add delay to reduce load\n IF @RowsAffected > 0\n WAITFOR DELAY '00:00:01';\n END;\nEND;\nGO\n```\n\n### Pagination\n\n```sql\nCREATE PROCEDURE usp_GetOrdersPaged\n @PageNumber INT = 1,\n @PageSize INT = 20,\n @TotalCount INT OUTPUT\nAS\nBEGIN\n SET NOCOUNT ON;\n\n -- Get total count\n SELECT @TotalCount = COUNT(*) FROM orders;\n\n -- Get page\n SELECT order_id, customer_id, total, order_date\n FROM orders\n ORDER BY order_date DESC\n OFFSET (@PageNumber - 1) * @PageSize ROWS\n FETCH NEXT @PageSize ROWS ONLY;\nEND;\nGO\n```\n\n### Upsert Pattern\n\n```sql\nCREATE PROCEDURE usp_UpsertCustomer\n @CustomerId INT,\n @Name NVARCHAR(100),\n @Email NVARCHAR(255)\nAS\nBEGIN\n SET NOCOUNT ON;\n\n MERGE INTO customers AS target\n USING (SELECT @CustomerId, @Name, @Email) AS source (id, name, email)\n ON target.customer_id = source.id\n WHEN MATCHED THEN\n UPDATE SET name = source.name, email = source.email, updated_at = GETDATE()\n WHEN NOT MATCHED THEN\n INSERT (customer_id, name, email, created_at)\n VALUES (source.id, source.name, source.email, GETDATE());\nEND;\nGO\n```\n\n### Error Logging\n\n```sql\nCREATE PROCEDURE usp_LogError\nAS\nBEGIN\n SET NOCOUNT ON;\n\n INSERT INTO error_log (\n error_number,\n error_message,\n error_severity,\n error_state,\n error_line,\n error_procedure,\n logged_at\n )\n VALUES (\n ERROR_NUMBER(),\n ERROR_MESSAGE(),\n ERROR_SEVERITY(),\n ERROR_STATE(),\n ERROR_LINE(),\n ERROR_PROCEDURE(),\n GETDATE()\n );\nEND;\nGO\n\n-- Usage\nBEGIN CATCH\n EXEC usp_LogError;\n THROW;\nEND CATCH;\n```\n\n## Metadata\n\n```sql\n-- View procedure definition\nEXEC sp_helptext 'usp_MyProcedure';\n\n-- Or\nSELECT OBJECT_DEFINITION(OBJECT_ID('usp_MyProcedure'));\n\n-- View parameters\nSELECT * FROM sys.parameters\nWHERE object_id = OBJECT_ID('usp_MyProcedure');\n\n-- View all procedures\nSELECT name, create_date, modify_date\nFROM sys.procedures\nWHERE schema_id = SCHEMA_ID('dbo');\n```\n\n## Drop Procedure\n\n```sql\nDROP PROCEDURE IF EXISTS usp_MyProcedure;\n-- Pre-2016:\nIF OBJECT_ID('usp_MyProcedure', 'P') IS NOT NULL\n DROP PROCEDURE usp_MyProcedure;\n```\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":7361,"content_sha256":"046004e691461f51ea46aed4f24b89ac892d48331a3ebe849340a61826e817cc"}],"content_json":{"type":"doc","content":[{"type":"heading","attrs":{"level":1},"content":[{"text":"T-SQL Core Knowledge","type":"text"}]},{"type":"blockquote","content":[{"type":"paragraph","content":[{"text":"Full Reference","type":"text","marks":[{"type":"strong"}]},{"text":": See ","type":"text"},{"text":"advanced.md","type":"text","marks":[{"type":"link","attrs":{"href":"advanced.md","title":null}}]},{"text":" for multi-statement TVFs, custom error messages, INSTEAD OF triggers, transaction isolation levels, cursors, dynamic SQL, and recursive CTEs.","type":"text"}]}]},{"type":"blockquote","content":[{"type":"paragraph","content":[{"text":"Deep Knowledge","type":"text","marks":[{"type":"strong"}]},{"text":": Use ","type":"text"},{"text":"mcp__documentation__fetch_docs","type":"text","marks":[{"type":"code_inline"}]},{"text":" with technology: ","type":"text"},{"text":"sqlserver","type":"text","marks":[{"type":"code_inline"}]},{"text":" for comprehensive documentation.","type":"text"}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Basic Structure","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Anonymous block\nBEGIN\n DECLARE @count INT = 0;\n SET @count = @count + 1;\n PRINT 'Count: ' + CAST(@count AS VARCHAR);\nEND;\nGO","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Variables","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"DECLARE @name VARCHAR(100) = 'John';\nDECLARE @age INT;\nDECLARE @salary DECIMAL(10,2), @bonus DECIMAL(10,2);\n\nSET @age = 25;\nSELECT @salary = salary FROM employees WHERE id = 1;\n\n-- Multiple assignments\nSELECT @salary = salary, @bonus = bonus\nFROM employees WHERE id = 1;\n\n-- Table variable\nDECLARE @employees TABLE (\n id INT,\n name VARCHAR(100),\n salary DECIMAL(10,2)\n);\n\nINSERT INTO @employees SELECT id, name, salary FROM employees;","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Stored Procedures","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Basic Procedure","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"CREATE OR ALTER PROCEDURE usp_GetEmployee\n @EmployeeId INT\nAS\nBEGIN\n SET NOCOUNT ON;\n\n SELECT employee_id, first_name, last_name, salary\n FROM employees\n WHERE employee_id = @EmployeeId;\nEND;\nGO\n\n-- Execute\nEXEC usp_GetEmployee @EmployeeId = 100;\n-- or\nEXEC usp_GetEmployee 100;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Procedure with OUTPUT Parameters","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"CREATE OR ALTER PROCEDURE usp_GetEmployeeStats\n @DeptId INT,\n @EmployeeCount INT OUTPUT,\n @TotalSalary DECIMAL(15,2) OUTPUT,\n @AvgSalary DECIMAL(15,2) OUTPUT\nAS\nBEGIN\n SET NOCOUNT ON;\n\n SELECT\n @EmployeeCount = COUNT(*),\n @TotalSalary = SUM(salary),\n @AvgSalary = AVG(salary)\n FROM employees\n WHERE department_id = @DeptId;\nEND;\nGO\n\n-- Call with OUTPUT\nDECLARE @Count INT, @Total DECIMAL(15,2), @Avg DECIMAL(15,2);\nEXEC usp_GetEmployeeStats\n @DeptId = 10,\n @EmployeeCount = @Count OUTPUT,\n @TotalSalary = @Total OUTPUT,\n @AvgSalary = @Avg OUTPUT;\n\nPRINT 'Count: ' + CAST(@Count AS VARCHAR);","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Procedure with Return Value","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"CREATE OR ALTER PROCEDURE usp_ValidateEmployee\n @EmployeeId INT\nAS\nBEGIN\n SET NOCOUNT ON;\n\n IF NOT EXISTS (SELECT 1 FROM employees WHERE employee_id = @EmployeeId)\n RETURN -1; -- Not found\n\n IF EXISTS (SELECT 1 FROM employees WHERE employee_id = @EmployeeId AND status = 'INACTIVE')\n RETURN -2; -- Inactive\n\n RETURN 0; -- Success\nEND;\nGO\n\n-- Check return value\nDECLARE @result INT;\nEXEC @result = usp_ValidateEmployee @EmployeeId = 100;\n\nIF @result = 0\n PRINT 'Valid';\nELSE IF @result = -1\n PRINT 'Employee not found';","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Functions","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Scalar Function","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"CREATE OR ALTER FUNCTION dbo.fn_CalculateBonus(\n @Salary DECIMAL(10,2),\n @YearsOfService INT\n)\nRETURNS DECIMAL(10,2)\nAS\nBEGIN\n DECLARE @Bonus DECIMAL(10,2);\n\n SET @Bonus = CASE\n WHEN @YearsOfService >= 10 THEN @Salary * 0.15\n WHEN @YearsOfService >= 5 THEN @Salary * 0.10\n ELSE @Salary * 0.05\n END;\n\n RETURN @Bonus;\nEND;\nGO\n\n-- Usage\nSELECT employee_id, salary,\n dbo.fn_CalculateBonus(salary, years_of_service) AS bonus\nFROM employees;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Inline Table-Valued Function","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"CREATE OR ALTER FUNCTION dbo.fn_GetDeptEmployees(\n @DeptId INT\n)\nRETURNS TABLE\nAS\nRETURN (\n SELECT employee_id, first_name, last_name, salary\n FROM employees\n WHERE department_id = @DeptId\n);\nGO\n\n-- Usage\nSELECT * FROM dbo.fn_GetDeptEmployees(10);","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Control Flow","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"IF...ELSE","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"DECLARE @status VARCHAR(20);\n\nIF @status = 'ACTIVE'\nBEGIN\n PRINT 'User is active';\n -- Multiple statements in BEGIN...END\nEND\nELSE IF @status = 'PENDING'\n PRINT 'User is pending'; -- Single statement, no BEGIN needed\nELSE\nBEGIN\n PRINT 'User is inactive';\nEND;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"CASE Expression","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"SELECT\n employee_id,\n salary,\n CASE\n WHEN salary >= 100000 THEN 'Executive'\n WHEN salary >= 50000 THEN 'Senior'\n WHEN salary >= 30000 THEN 'Mid'\n ELSE 'Junior'\n END AS level\nFROM employees;\n\n-- Simple CASE\nSELECT\n employee_id,\n CASE status\n WHEN 'A' THEN 'Active'\n WHEN 'I' THEN 'Inactive'\n ELSE 'Unknown'\n END AS status_name\nFROM employees;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"WHILE Loop","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"DECLARE @counter INT = 1;\n\nWHILE @counter \u003c= 10\nBEGIN\n PRINT 'Counter: ' + CAST(@counter AS VARCHAR);\n SET @counter = @counter + 1;\n\n IF @counter = 5\n CONTINUE; -- Skip to next iteration\n\n IF @counter = 8\n BREAK; -- Exit loop\nEND;","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Error Handling","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"TRY...CATCH","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"BEGIN TRY\n BEGIN TRANSACTION;\n\n UPDATE accounts SET balance = balance - 100 WHERE id = 1;\n UPDATE accounts SET balance = balance + 100 WHERE id = 2;\n\n COMMIT TRANSACTION;\nEND TRY\nBEGIN CATCH\n IF @@TRANCOUNT > 0\n ROLLBACK TRANSACTION;\n\n -- Error information\n DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();\n DECLARE @ErrorSeverity INT = ERROR_SEVERITY();\n DECLARE @ErrorState INT = ERROR_STATE();\n\n -- Re-throw or log\n RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);\nEND CATCH;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Error Functions","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Function","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Description","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"ERROR_NUMBER()","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Error number","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"ERROR_MESSAGE()","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Error message","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"ERROR_SEVERITY()","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Error severity (0-25)","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"ERROR_STATE()","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Error state","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"ERROR_LINE()","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Line number where error occurred","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"ERROR_PROCEDURE()","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Stored procedure name","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"THROW vs RAISERROR","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- THROW (SQL Server 2012+, preferred)\nTHROW 50001, 'Custom error message', 1;\n\n-- THROW without parameters re-throws current error\nBEGIN CATCH\n INSERT INTO error_log (message, error_time)\n VALUES (ERROR_MESSAGE(), GETDATE());\n\n THROW; -- Re-throw original error\nEND CATCH;\n\n-- RAISERROR (legacy)\nRAISERROR('Error: %s', 16, 1, @ErrorMessage);","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Triggers","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"DML Trigger","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"CREATE OR ALTER TRIGGER tr_employees_audit\nON employees\nAFTER INSERT, UPDATE, DELETE\nAS\nBEGIN\n SET NOCOUNT ON;\n\n -- Handle INSERT\n IF EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)\n BEGIN\n INSERT INTO employees_audit (action, employee_id, new_salary, changed_by, changed_at)\n SELECT 'INSERT', employee_id, salary, SYSTEM_USER, GETDATE()\n FROM inserted;\n END\n\n -- Handle UPDATE\n IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)\n BEGIN\n INSERT INTO employees_audit (action, employee_id, old_salary, new_salary, changed_by, changed_at)\n SELECT 'UPDATE', i.employee_id, d.salary, i.salary, SYSTEM_USER, GETDATE()\n FROM inserted i\n INNER JOIN deleted d ON i.employee_id = d.employee_id;\n END\n\n -- Handle DELETE\n IF NOT EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)\n BEGIN\n INSERT INTO employees_audit (action, employee_id, old_salary, changed_by, changed_at)\n SELECT 'DELETE', employee_id, salary, SYSTEM_USER, GETDATE()\n FROM deleted;\n END\nEND;\nGO","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Transactions","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"BEGIN TRANSACTION;\n-- or\nBEGIN TRAN;\n\nSAVE TRANSACTION SavePoint1;\n\n-- Rollback to savepoint\nROLLBACK TRANSACTION SavePoint1;\n\nCOMMIT TRANSACTION;\n-- or\nCOMMIT;\n\n-- Check transaction count\nSELECT @@TRANCOUNT;\n\n-- Named transaction\nBEGIN TRANSACTION MyTransaction;\nCOMMIT TRANSACTION MyTransaction;","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Common Table Expressions (CTE)","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":";WITH dept_stats AS (\n SELECT\n department_id,\n COUNT(*) AS emp_count,\n AVG(salary) AS avg_salary\n FROM employees\n GROUP BY department_id\n)\nSELECT d.department_name, ds.emp_count, ds.avg_salary\nFROM departments d\nINNER JOIN dept_stats ds ON d.department_id = ds.department_id;","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Best Practices","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"DO","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use SET NOCOUNT ON in procedures","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use TRY...CATCH for error handling","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use sp_executesql for dynamic SQL","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use FAST_FORWARD cursors when possible","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use table-valued parameters for batch operations","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Always qualify object names with schema","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"DON'T","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use SELECT * in production code","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Build dynamic SQL with string concatenation","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use cursors when set-based operations work","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Ignore error handling","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use deprecated features (GROUP BY ALL, etc.)","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"When NOT to Use This Skill","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Basic SQL Server SQL","type":"text","marks":[{"type":"strong"}]},{"text":" - Use ","type":"text"},{"text":"sqlserver","type":"text","marks":[{"type":"code_inline"}]},{"text":" skill for data types, indexes, temporal tables","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"PL/pgSQL (PostgreSQL)","type":"text","marks":[{"type":"strong"}]},{"text":" - Use ","type":"text"},{"text":"plpgsql","type":"text","marks":[{"type":"code_inline"}]},{"text":" skill for PostgreSQL procedures","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"PL/SQL (Oracle)","type":"text","marks":[{"type":"strong"}]},{"text":" - Use ","type":"text"},{"text":"plsql","type":"text","marks":[{"type":"code_inline"}]},{"text":" skill for Oracle procedures","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Basic SQL","type":"text","marks":[{"type":"strong"}]},{"text":" - Use ","type":"text"},{"text":"sql-fundamentals","type":"text","marks":[{"type":"code_inline"}]},{"text":" for ANSI SQL basics","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Anti-Patterns","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Anti-Pattern","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Problem","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Solution","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Not using TRY...CATCH","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Silent errors","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Add error handling blocks","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Using RAISERROR","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Deprecated pattern","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Use THROW (SQL 2012+)","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Missing SET NOCOUNT ON","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Performance overhead","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Add to all procedures","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"String concatenation for dynamic SQL","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"SQL injection","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Use sp_executesql with parameters","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Using SELECT without ORDER BY for TOP","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Non-deterministic results","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Always specify ORDER BY","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Cursors when set-based works","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Poor performance","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Rewrite as set operations","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Quick Troubleshooting","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Problem","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Diagnostic","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Fix","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Transaction uncommitted","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"SELECT @@TRANCOUNT","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Add COMMIT or ROLLBACK","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Procedure slow","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Execution plan in SSMS","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Add indexes, rewrite queries","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Error swallowed","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Check CATCH block","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Ensure THROW or logging","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Deadlock victim","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Extended Events","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Consistent access order","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Parameter sniffing","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Compare plans","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Use OPTION (RECOMPILE) or local variables","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Reference Documentation","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Procedures","type":"text","marks":[{"type":"link","attrs":{"href":"quick-ref/procedures.md","title":null}}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Functions","type":"text","marks":[{"type":"link","attrs":{"href":"quick-ref/functions.md","title":null}}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Error Handling","type":"text","marks":[{"type":"link","attrs":{"href":"quick-ref/error-handling.md","title":null}}]}]}]}]},{"type":"hr","attrs":{"markup":"---"}}]},"metadata":{"date":"2026-06-05","name":"tsql","author":"@skillopedia","source":{"stars":19,"repo_name":"claude-dev-suite","origin_url":"https://github.com/claude-dev-suite/claude-dev-suite/blob/HEAD/skills/databases/tsql/SKILL.md","repo_owner":"claude-dev-suite","body_sha256":"369a2f006feac64adc5af7b45615732cd01bbdeaa540a19d080213eaf06cd8ec","cluster_key":"f87cec224f0d03dbbe4176bc7e9886f4ea6027c85fdce7fb3dd407f686df8295","clean_bundle":{"format":"clean-skill-bundle-v1","source":"claude-dev-suite/claude-dev-suite/skills/databases/tsql/SKILL.md","attachments":[{"id":"9664cd56-89bd-564f-9d94-33d83d00f7f8","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/9664cd56-89bd-564f-9d94-33d83d00f7f8/attachment.md","path":"advanced.md","size":5909,"sha256":"40e0ae40a956737cc25658ecedc8092b749f78f7145ac6796d88c0b050ae2dc4","contentType":"text/markdown; charset=utf-8"},{"id":"5f7f68c8-d2a5-5b3a-9cae-b267fcadcaa5","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/5f7f68c8-d2a5-5b3a-9cae-b267fcadcaa5/attachment.md","path":"quick-ref/error-handling.md","size":6942,"sha256":"893722ea244183cb2856a243f611a0967c891b1422b4332bf0908a47a549bb54","contentType":"text/markdown; charset=utf-8"},{"id":"f89a3a2c-a495-596f-887d-230ae4d332ae","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/f89a3a2c-a495-596f-887d-230ae4d332ae/attachment.md","path":"quick-ref/functions.md","size":6541,"sha256":"01a9184fd0aec9dfbe9afb978c6fc8d15f1338c7e96ec7fcf65a6a592d716e8b","contentType":"text/markdown; charset=utf-8"},{"id":"537b265c-302e-5fd8-a574-904b39d66757","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/537b265c-302e-5fd8-a574-904b39d66757/attachment.md","path":"quick-ref/procedures.md","size":7361,"sha256":"046004e691461f51ea46aed4f24b89ac892d48331a3ebe849340a61826e817cc","contentType":"text/markdown; charset=utf-8"}],"bundle_sha256":"16b3b54c3b47d464e507ddc8a5ef0b4df4434977b28d87118d2311534a527f18","attachment_count":4,"text_attachments":4,"attachment_storage":"skillopedia-attachments-v1","binary_attachments":0,"excluded_attachments":[]},"cluster_size":1,"skill_md_path":"skills/databases/tsql/SKILL.md","import_metadata":{"date":"2026-06-05","author":"@skillopedia","version":"v1","category":"data-analytics","category_label":"Data"},"exact_dupes_collapsed_into_this":0},"version":"v1","category":"data-analytics","import_tag":"clean-skills-v1","description":"Microsoft SQL Server T-SQL procedural language. Covers stored procedures,\nfunctions, triggers, error handling, and SQL Server-specific features.\nUse for SQL Server database server-side programming.\n\nUSE WHEN: user mentions \"tsql\", \"T-SQL\", \"SQL Server procedures\",\n\"SQL Server functions\", \"TRY...CATCH\", \"THROW\", \"sp_executesql\", \"SSMS\"\n\nDO NOT USE FOR: basic SQL Server SQL - use `sqlserver` instead,\nPostgreSQL - use `plpgsql` instead, Oracle - use `plsql` instead\n","allowed-tools":"Read, Grep, Glob, Write, Edit"}},"renderedAt":1782987172344}

Important: agents should read /llm.txt, /llms.txt, or /.well-known/skills.json to discover the public Skillopedia API.