SQLScript Validation Command

Validate SQLScript code for syntax errors, pattern violations, and best practices. Reports issues and offers to auto-fix common problems.

Usage

/sqlscript-validate [file_path]
/sqlscript-validate [file_path] --fix
/sqlscript-validate --all

Validation Categories

1. Critical Issues (Must Fix)

• Missing EXIT HANDLER: Procedures without error handling
• Unclosed Cursors: Cursor opened but not closed in all paths
• Hardcoded Schema Names: Schema names embedded in code
• SQL Injection Risk: Dynamic SQL without proper escaping
• Missing LANGUAGE SQLSCRIPT: Required clause missing

2. Warnings (Should Fix)

• Cursor in Loop: Performance anti-pattern
• SELECT * Usage: Should specify columns explicitly
• Missing WHERE Clause: DELETE/UPDATE without filter
• Imperative Logic Overuse: Could be declarative
• Missing Default Values: Parameters without defaults

3. Suggestions (Consider)

• Naming Convention: Non-standard prefixes
• Comment Density: Insufficient documentation
• Code Organization: Logic ordering recommendations
• AMDP Compliance: AMDP-specific issues

Validation Process

1. Read the specified file(s)
2. Parse SQLScript structure
3. Run validation checks in order:
   • Syntax validation
   • Error handling completeness
   • Performance patterns
   • Security checks
   • Style/naming conventions
4. Categorize findings by severity
5. Report issues with line numbers
6. Offer auto-fixes for applicable issues

Auto-Fix Capabilities

The following issues can be auto-fixed:

Issue	Auto-Fix Action
Missing EXIT HANDLER	Add generic exception handler
Unclosed cursor	Add CLOSE statement in finally block
Missing semicolon	Add semicolon at statement end
SELECT * in procedure	Cannot auto-fix (requires column selection)
Hardcoded schema	Replace with parameter or DEFAULT SCHEMA
Missing LANGUAGE SQLSCRIPT	Add the clause

Output Format

## SQLScript Validation Report

**File**: [filename]
**Lines**: [count]
**Status**: [PASS/FAIL]

### Critical Issues (X found)
1. [Line X]: [Issue description]
   **Current**: `[code snippet]`
   **Fix**: `[corrected code]`
   **Auto-fix available**: Yes/No

### Warnings (X found)
[Similar format]

### Suggestions (X found)
[Similar format]

### Summary
- Critical: X
- Warnings: X
- Suggestions: X

### Auto-Fix Available
Would you like me to automatically fix the X issues that have auto-fix available?

Examples

Validate Single File

/sqlscript-validate src/procedures/get_orders.sql

Validate and Auto-Fix

/sqlscript-validate src/procedures/get_orders.sql --fix

Validate All SQLScript Files

/sqlscript-validate --all

Implementation Instructions

When this command is invoked:

1. If a file path is provided, read that file
2. If --all is specified, find all .sql files and .abap files containing AMDP
3. For each file:
   • Parse the content to identify procedure/function boundaries
   • Check for each validation rule
   • Record issues with line numbers
4. Present findings in the format above
5. If --fix is specified or user confirms, apply auto-fixes
6. After fixes, re-validate to confirm resolution

Validation Rules Details

Missing EXIT HANDLER

-- BAD: No error handling
CREATE PROCEDURE test()
AS
BEGIN
  INSERT INTO table VALUES (1);
END;

-- GOOD: Has error handling
CREATE PROCEDURE test()
AS
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
    SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;
  INSERT INTO table VALUES (1);
END;

Unclosed Cursor

-- BAD: Cursor not closed
OPEN cur;
FETCH cur INTO var;
-- Missing: CLOSE cur;

-- GOOD: Cursor properly closed
OPEN cur;
FETCH cur INTO var;
CLOSE cur;

Hardcoded Schema

-- BAD: Hardcoded schema
SELECT * FROM "MYSCHEMA"."MYTABLE";

-- GOOD: Use DEFAULT SCHEMA or parameter
SELECT * FROM "MYTABLE";  -- With DEFAULT SCHEMA clause