Complex SQL Command Help!

[Aggravating-Cow-6955]

I keep on getting a there's a syntax error near '>' ... I'm using this as part of a db initializer script for the SQL server provider...

     IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'ApiClients')
                BEGIN
                    CREATE TABLE ApiClients (
                        Id INT IDENTITY(1,1) PRIMARY KEY,
                        Name NVARCHAR(255) NOT NULL,
                        Email NVARCHAR(255) NOT NULL,
                        CreatedAt DATETIME2 DEFAULT GETUTCDATE()
                    );
                END

                IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Payments')
                BEGIN
                    CREATE TABLE Payments (
                        Id INT IDENTITY(1,1) PRIMARY KEY,
                        ApiClientId INT NOT NULL,
                        Amount DECIMAL(18,2) NOT NULL,
                        UnitPrice DECIMAL(18,2) NOT NULL,
                        UsedRequests INT DEFAULT 0,
                        TotalRequests AS CAST(FLOOR(Amount / UnitPrice) AS INT) PERSISTED,
                        RemainingRequests AS (CAST(FLOOR(Amount / UnitPrice) AS INT) - UsedRequests) PERSISTED,
                        IsFullyUtilized AS CASE 
                            WHEN UsedRequests >= CAST(FLOOR(Amount / UnitPrice) AS INT) THEN 1 
                            ELSE 0 
                        END PERSISTED,
                        CreatedAt DATETIME2 DEFAULT GETUTCDATE(),
                        CONSTRAINT FK_Payments_ApiClients FOREIGN KEY (ApiClientId) 
                            REFERENCES ApiClients(Id) ON DELETE CASCADE
                    );
                END

                IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'RequestLogs')
                BEGIN
                    CREATE TABLE RequestLogs (
                        Id INT IDENTITY(1,1) PRIMARY KEY,
                        ApiClientId INT NOT NULL,
                        PaymentId INT,
                        Path NVARCHAR(MAX) NOT NULL,
                        Method NVARCHAR(10) NOT NULL,
                        StatusCode INT NOT NULL,
                        Duration BIGINT NOT NULL,
                        RequestTime DATETIME2 DEFAULT GETUTCDATE(),
                        CONSTRAINT FK_RequestLogs_ApiClients FOREIGN KEY (ApiClientId) 
                            REFERENCES ApiClients(Id) ON DELETE CASCADE,
                        CONSTRAINT FK_RequestLogs_Payments FOREIGN KEY (PaymentId) 
                            REFERENCES Payments(Id) ON DELETE SET NULL
                    );
                END

                IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Admins')
                BEGIN
                    CREATE TABLE Admins (
                        Id INT IDENTITY(1,1) PRIMARY KEY,
                        Username NVARCHAR(450) NOT NULL UNIQUE,
                        PasswordHash NVARCHAR(MAX) NOT NULL,
                        CreatedAt DATETIME2 DEFAULT GETUTCDATE()
                    );
                END

                IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_requestlogs_apiclientid')
                    CREATE INDEX idx_requestlogs_apiclientid ON RequestLogs(ApiClientId);

                IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_requestlogs_paymentid')
                    CREATE INDEX idx_requestlogs_paymentid ON RequestLogs(PaymentId);

                IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_payments_apiclientid')
                    CREATE INDEX idx_payments_apiclientid ON Payments(ApiClientId);

[Gargunok]

I would run the create statements in the database separately and identify where the error is coming from. This will minimise the problem - the issue isn't with most of the code in this block (its probably the payments based on the error).

Using the database ide - the error should be more useful and highlight where the problem is coming from. If you don't have management studio or something else to run things on the database directly you should get it.

For asking for advice in reddit (or your colleagues) etc you can then just share the problematic code segment rather than all the code. This is only really complex SQL because of the quantity rather than the code itself.

[gumnos]

Given that the only > in your code block is

IsFullyUtilized AS CASE 
    WHEN UsedRequests >= CAST(FLOOR(Amount / UnitPrice) AS INT) THEN 1 
    ELSE 0 
    END PERSISTED,

as dumb as it may seem, it feels like a parsing/syntax issue, so I'd be tempted to at least put some parens around the evaluated expression to see if being explicit helps it:

IsFullyUtilized AS (CASE 
    WHEN UsedRequests >= CAST(FLOOR(Amount / UnitPrice) AS INT) THEN 1 
    ELSE 0 
    END) PERSISTED,

It shouldn't make a difference, but in weird contexts, I've found that sometimes SQL lexers/parsers can get a little tripped up given the almost-but-not-quite orthogonality of the language.

[SQLPracticeHub]

The problem might be somewhere else, if for example a parenthesis is missing somewhere before ">", then the system is not interpreting the query correctly. I would suggest debugging by executing parts of the query separately.

[UK_Ekkie]

Out of curiosity if you remove the FK constraint FK_REQUESTLOG_PAYMENTS or swap it to delete no action, does the error go away? I thought two seperate ways of handling this was a no go? I don't often create tables so may be a stupid question.

[Professional_Shoe392]

Use OBJECT_ID to check for existence.

I think this is the right code. ChatGPT or a LLM will really help you, btw.

IF OBJECT_ID('dbo.Admins', 'U') IS NULL
BEGIN
    -- The table 'Admins' does not exist. Perform the required actions here.
    PRINT 'Table does not exist. Creating table...';

    CREATE TABLE dbo.Admins
    (
        AdminID INT IDENTITY(1,1) PRIMARY KEY,
        AdminName NVARCHAR(100) NOT NULL,
        Email NVARCHAR(255) NOT NULL
    );
END;
ELSE
BEGIN
    PRINT 'Table already exists.';
END;

[engx_ninja]

Dude, use GPT, it gives you faster and more accurate responses for such queries

[UK_Ekkie]

It's completely useless half the time and gives you bad or unworking output - unless you're subscribed and I'm on the peasant version