Bug Report: Invalid CASE Expression Placement in Generated INSERT Statements

[Merokz]

Bug Report: Invalid CASE Expression Placement in Generated INSERT Statements

Description:

When generating INSERT procedures for tables that have a VALID column, qss appears to place the CASE expression for VALID directly in the VALUES clause without mapping it explicitly to the VALID column. This leads to a syntax error in the generated SQL, making the script fail to execute.

Steps to Reproduce:

1. Use qss to generate a table and CRUD stored procedures that include the VALID column (e.g., sp:*).
2. Review the generated INSERT stored procedure for that table.
3. Observe that the CASE expression used to determine the VALID column’s value is included directly in the VALUES clause, not aligned with the listed columns.

Expected Behavior:

The CASE expression should be included as the value for the VALID column in the VALUES list. For example:

INSERT INTO [schema].[TABLE] (
    USERNAME,
    DISPLAY_NAME,
    BIO,
    EMAIL,
    PASSWORD_HASH,
    PROFILE_PICTURE,
    CREATED_AT,
    LAST_ONLINE_AT,
    VALID, 
    MOD_USER, 
    MOD_TIMESTAMP, 
    CR_USER, 
    CR_TIMESTAMP
)
VALUES (
    @USERNAME,
    @DISPLAY_NAME,
    @BIO,
    @EMAIL,
    @PASSWORD_HASH,
    @PROFILE_PICTURE,
    @CREATED_AT,
    @LAST_ONLINE_AT,
    CASE
        WHEN @VALID IN (0,1) THEN @VALID
        WHEN @VALID IS NULL THEN 1
        ELSE 0
    END,
    CURRENT_USER, 
    CURRENT_TIMESTAMP, 
    CURRENT_USER, 
    CURRENT_TIMESTAMP
);

Actual Behavior:
The generated code places the CASE expression directly after the LAST_ONLINE_AT column’s value and before listing VALID in the INSERT column list. This results in an error:

Msg 156, Level 15, State 1, Line X Incorrect syntax near the keyword 'CASE'.

Additional Information:

• The issue seems related to how qss handles the VALID column’s default logic.
• Manually editing the generated code to ensure the CASE expression aligns with the VALID column resolves the issue.

Environment:

• Database: MSSQL
• qss Version: [Please specify the version if known]
• OS: [Your OS and version]

Possible Fix:

Update the code generation template so that the CASE expression is placed as the value for VALID explicitly, rather than inserting it without a matching column. Ensure that the column and its corresponding value (the CASE expression) align one-to-one.

Request:

Please investigate whether the code generation logic can be adjusted so the VALID field’s CASE expression is always properly mapped to the VALID column, preventing this syntax error.

[Grisgram]

runs fine on local mssql.
maybe an azure... "feature"?
to me, this is a correct statement, it is directly aligned, as the insert rule demands the columns to be in the same order as in the field list. this is the case.

the values list is read in the same order as you provide the field list. statement above looks correct and in a simulated table local on my test environment this insert trigger reacts correctly, as it should.

what do you mean with rather than inserting it without a matching column? None of these columns has any special "alignment", they are matched by index, first-to-first, second-to-second and so on.

sorry, can not reproduce this one.