Actionable rules for designing, writing, testing and optimizing stored procedures with an emphasis on T-SQL / SQL Server while remaining portable to Azure SQL, Amazon Redshift and Snowflake.
Every database developer knows the pain: stored procedures that work in development but fail mysteriously in production, code that becomes unmaintainable after the first few iterations, and performance bottlenecks that emerge at the worst possible moments. You've probably spent countless hours debugging procedures that should have been straightforward, only to discover issues that proper standards would have prevented entirely.
This isn't about basic SQL skills—you already know how to write queries. This is about the gap between writing SQL that works and writing stored procedures that scale, perform, and maintain themselves across enterprise environments.
Database teams face predictable, expensive problems when stored procedures lack consistent standards:
Production Failures: Procedures that work perfectly in development break when real data volumes hit, or when they encounter edge cases that weren't tested. The famous "it works on my machine" problem, but with databases.
Security Vulnerabilities: Dynamic SQL construction without proper parameterization creates injection risks. Direct table access instead of procedure-level permissions creates unnecessary attack surfaces.
Performance Degradation: Procedures written without consideration for execution plans, parameter sniffing, or proper indexing strategies become bottlenecks as data grows.
Cross-Platform Headaches: Code that works perfectly on SQL Server fails when migrated to Azure SQL Database, Redshift, or Snowflake due to subtle platform differences.
Maintenance Nightmares: Procedures without consistent structure, documentation, or error handling become technical debt that compounds over time.
These Cursor Rules establish enterprise-grade standards for stored procedure development across SQL Server, Azure SQL Database, Amazon Redshift, and Snowflake. They're designed to eliminate the common failure patterns while maintaining the flexibility you need for complex database operations.
The framework enforces set-based, idempotent operations with comprehensive error handling, security-first design, and cross-platform compatibility. Every procedure follows the same structured approach, making your entire codebase predictable and maintainable.
-- Standard error handling pattern across all procedures
BEGIN TRY
BEGIN TRAN
-- Your business logic here
COMMIT TRAN
RETURN 0
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRAN
THROW -- Re-throw with full context
END CATCH
Every procedure follows the same transaction and error handling pattern, eliminating the guesswork and ensuring consistent behavior under failure conditions.
Instead of granting table-level permissions:
-- Application gets EXECUTE rights only
GRANT EXECUTE ON dbo.get_Order_Totals TO app_role;
-- No direct table access needed
The rules enforce procedure-level security, eliminating direct table access and reducing your attack surface significantly.
The rules include platform-specific patterns for SQL Server, Azure SQL, Redshift, and Snowflake, so you can deploy the same logical procedures across different environments:
-- SQL Server / Azure SQL
BEGIN TRY...END TRY
-- Redshift equivalent
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE 'Error: %', SQLERRM;
-- SARGable predicates enforced
WHERE OrderDate >= @StartDate
AND OrderDate < DATEADD(day, 1, @EndDate)
-- Not: WHERE CONVERT(date, OrderDate) = @SearchDate
The rules prevent common performance anti-patterns while establishing indexing and query optimization standards.
Before: You write a procedure on SQL Server, then spend hours rewriting it for Azure SQL Database because of subtle platform differences. Redshift deployment requires another complete rewrite.
After: The rules provide platform-specific patterns upfront:
-- Works across SQL Server, Azure SQL, with documented Redshift variations
SET NOCOUNT ON;
SET XACT_ABORT ON; -- SQL Server/Azure only
-- Redshift: Use explicit transaction blocks instead
Time Saved: 4-6 hours per procedure when deploying across platforms.
Before: A procedure fails in production with cryptic error messages. You spend time recreating the exact conditions, adding debug statements, and deploying fixes iteratively.
After: Standardized error handling captures full context automatically:
BEGIN CATCH
SELECT
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage,
ERROR_PROCEDURE() as ErrorProcedure,
@@TRANCOUNT as TransactionCount;
IF @@TRANCOUNT > 0 ROLLBACK;
THROW;
END CATCH
Result: First failure provides complete diagnostic information, reducing debugging cycles from hours to minutes.
Before: You handle parameter validation inconsistently, sometimes building dynamic SQL with string concatenation (security risk), other times forgetting validation entirely.
After: Every procedure follows the same validation pattern:
-- Parameter validation template
IF @StartDate IS NULL OR @EndDate IS NULL
BEGIN
RAISERROR('StartDate and EndDate parameters are required', 16, 1);
RETURN 1;
END
-- Parameterized queries enforced
EXEC sp_executesql @sql, N'@StartDate DATE, @EndDate DATE', @StartDate, @EndDate;
Security Impact: Eliminates SQL injection vectors while ensuring consistent input validation.
Copy the complete rules configuration to your .cursorrules file in your database project root:
# In your database project directory
touch .cursorrules
# Paste the complete Stored Procedure Excellence Rule Set
Every new stored procedure starts with this template:
/*
Procedure : dbo.get_Order_Totals
Purpose : Returns aggregated totals per order
Params : @StartDate DATE, @EndDate DATE
Returns : Result set, ReturnCode INT (0=OK, >0=Error)
Notes : Requires IX_Orders_Date index
ChangeLog : v1.0.0 2024-11-20 Initial version
*/
CREATE OR ALTER PROCEDURE dbo.get_Order_Totals
@StartDate DATE = NULL,
@EndDate DATE = NULL
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
-- Parameter validation
IF @StartDate IS NULL OR @EndDate IS NULL
BEGIN
RAISERROR('Date parameters required', 16, 1);
RETURN 1;
END
BEGIN TRY
BEGIN TRAN
-- Main logic here
SELECT
o.OrderID,
SUM(od.Quantity * od.UnitPrice) as OrderTotal
FROM dbo.Orders o
INNER JOIN dbo.OrderDetails od ON o.OrderID = od.OrderID
WHERE o.OrderDate >= @StartDate
AND o.OrderDate < DATEADD(day, 1, @EndDate)
GROUP BY o.OrderID;
COMMIT TRAN
RETURN 0;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRAN;
THROW;
END CATCH
Set up role-based security for all database access:
-- Create application role
CREATE ROLE app_data_access;
-- Grant execute permissions, not table access
GRANT EXECUTE ON dbo.get_Order_Totals TO app_data_access;
-- Application connects using this role
-- No direct table permissions needed
Enable Query Store for automatic performance tracking:
ALTER DATABASE YourDatabase
SET QUERY_STORE = ON
(OPERATION_MODE = READ_WRITE,
DATA_FLUSH_INTERVAL_SECONDS = 900,
QUERY_CAPTURE_MODE = AUTO);
SQL Server/Azure SQL: Full TRY...CATCH support with Query Store integration for automatic performance regression detection.
Amazon Redshift: Explicit transaction management with PL/pgSQL error handling patterns that mirror SQL Server behavior.
Snowflake: JavaScript API integration for complex logic with SQL optimization for data operations.
The rules are immediately applicable to your existing stored procedure development. Start with your next procedure—apply the template, follow the error handling patterns, and implement the security model. You'll see immediate improvements in code quality and reliability.
For existing procedures, focus on the highest-risk ones first: those with dynamic SQL, complex business logic, or frequent production issues. Apply the rules incrementally, and you'll quickly build a more maintainable and secure database tier.
Your stored procedures become predictable, secure, and maintainable by default. No more guessing about error handling, security patterns, or cross-platform compatibility—the rules handle the boilerplate so you can focus on the business logic that matters.
You are an expert in T-SQL, SQL Server, Azure SQL Database, Amazon Redshift, and Snowflake scripting.
Key Principles
- Favor clear, set-based, idempotent code over cursor/row-by-row logic.
- Make every procedure safe to re-run; no hidden side effects.
- Keep the public contract (name, parameters, output) stable; internal tables may evolve.
- Default to least-privilege security: EXECUTE rights only, no direct table access.
- Separate data-access logic (procedures) from business rules (application/service layer).
- Never assume session state (ANSI settings, language, dateformat); set them explicitly at the start.
- Optimize first for readability, then for speed—premature tuning obscures intent.
T-SQL / Stored-Procedure Rules
- Naming
• Format: <Schema>.<Verb>_<Entity>[ _<Qualifier>] (e.g., dbo.get_Order_Totals).
• Avoid the sp_ prefix; SQL Server searches master first causing delay.
- Parameters
• Always parameterize; never concatenate user input into dynamic SQL.
• Use default NULLs for optional params; validate immediately and EXIT if invalid.
• For multi-value filters pass table-valued parameters (TVPs) or JSON; split in a temp table.
- Body Layout (top-down)
1. SET NOCOUNT ON;
2. SET XACT_ABORT ON; (SQL Server)
3. DECLARE/SET constants.
4. Validate parameters (RETURN 0 on bad input).
5. BEGIN TRY … BEGIN TRAN … main logic … COMMIT;
6. BEGIN CATCH … ROLLBACK … re-throw;
7. RETURN @ReturnCode.
- Query Writing
• Never SELECT *; list columns explicitly.
• Always prefix objects with schema.
• Use EXISTS instead of COUNT(*)>0 for checks.
• Use SARGable predicates; avoid functions on indexed columns in WHERE.
• Prefer MERGE only for audit loading; otherwise use separate INSERT/UPDATE with OUTPUT.
- Temporary Objects
• Use #local temp tables for large data reuse; drop at end.
• Use @table variables for <100 rows to avoid unnecessary logging.
- Output
• Return data sets via SELECT statements; return status via RETURN INT or OUTPUT params, not both.
• Never print/log sensitive data in production.
Error Handling & Validation
- Wrap all DML in TRY…CATCH.
- In CATCH capture ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_PROCEDURE(), @@TRANCOUNT.
- If @@TRANCOUNT>0 then ROLLBACK; afterwards RAISEERROR/THROW with same message.
- Surface friendly messages (50000–59999 error numbers) to client; hide internal details.
- For Redshift/Snowflake (no TRY…CATCH) use scoped transactions and RETURN result code table.
Framework-Specific Rules
SQL Server / Azure SQL
- Enable Query Store in ALL databases; use FORCE_LAST_GOOD_PLAN for regression.
- Index Hints: avoid unless absolutely required; document and revisit quarterly.
- Use SSDT database projects; store procs under /StoredProcedures/<schema>.
Amazon Redshift
- Two transaction modes: AUTOCOMMIT & explicit. Always start explicit transaction in stored procs for atomicity.
- Use PL/pgSQL syntax; use EXCEPTION … RAISE to mimic TRY…CATCH.
Snowflake
- Prefer JavaScript API for complex flow; keep SQL in call statements.
- Set error_handling = 'CONTINUE' only inside controlled loops; otherwise default.
Testing
- Unit: Use tSQLt or pgTAP equivalents; each test should set up data, EXEC proc, assert output.
- Integration: Run in dedicated schema copied from production mask.
- Regression: Capture query plans in Query Store baseline and compare after code change.
Performance & Optimization
- Capture actual plans with SET STATISTICS TIME, IO ON.
- Check sys.dm_exec_query_stats for cpu_time + total_logical_reads; tune top offenders.
- Use index-aligned temp tables to avoid spills.
- Batch modifications: 1,000–5,000 row chunks to keep log flushes small.
Security
- Revoke direct table CRUD from app roles; GRANT EXECUTE ON proc instead.
- For dynamic SQL use sp_executesql with @paramlist; QUOTENAME any schema/object names.
- Log all elevation attempts in dbo.AuditSecurityEvents table.
Versioning & Deployment
- Keep one file per procedure; include CREATE OR ALTER.
- Use Semantic Version tag in header comment (e.g., --v1.3.2 2024-05-01).
- Deploy via DACPAC or Flyway; auto-generate checksum to detect drift.
Documentation
- Header block template:
/*
Procedure : dbo.get_Order_Totals
Purpose : Returns aggregated totals per order
Params : @StartDate DATE (inclusive), @EndDate DATE (inclusive)
Returns : Result set, ReturnCode INT (0=OK, >0=Error)
Notes : Requires IX_Orders_Date index
ChangeLog : v1.3.2 2024-05-01 JS – Added TVP filter
*/
- Maintain procedure catalog in Data Dictionary; include sample call and expected result.