Customer support ticketing system in SQL Serve

Customer support ticketing system in SQL Server with a well-structured set of tables and stored procedures, including full examples for:

• ✅ Insert ticket

• ✏️ Update ticket

• 🔍 Search/filter tickets

• 🔄 Assign/close/reopen

• 📄 Pagination

• 🧾 Audit logging

• 📋 Supporting tables (e.g., users, categories, statuses)

---

📦 1. Database Table Structure for Support Ticket System

🎫 SupportTickets

CREATE TABLE SupportTickets (
    TicketId INT IDENTITY(1,1) PRIMARY KEY,
    Title NVARCHAR(200),
    Description NVARCHAR(MAX),
    StatusId INT,
    CategoryId INT,
    PriorityId INT,
    CustomerId INT,
    AssignedTo INT NULL,
    CreatedBy NVARCHAR(50),
    CreatedDate DATETIME DEFAULT GETDATE(),
    UpdatedBy NVARCHAR(50),
    UpdatedDate DATETIME,
    IsDeleted BIT DEFAULT 0
);

👤 Users

CREATE TABLE Users (
    UserId INT IDENTITY(1,1) PRIMARY KEY,
    UserName NVARCHAR(100),
    Role NVARCHAR(50)  -- e.g., Customer, Agent, Admin
);

📂 Categories

CREATE TABLE Categories (
    CategoryId INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100)
);

🚦 Statuses

CREATE TABLE Statuses (
    StatusId INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(50)  -- e.g., Open, In Progress, Closed, Reopened
);

🏷️ Priorities

CREATE TABLE Priorities (
    PriorityId INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(50)  -- e.g., Low, Medium, High, Urgent
);

📜 TicketAuditLog

CREATE TABLE TicketAuditLog (
    AuditId INT IDENTITY(1,1) PRIMARY KEY,
    TicketId INT,
    Action NVARCHAR(100),
    PerformedBy NVARCHAR(50),
    ActionDate DATETIME DEFAULT GETDATE(),
    Notes NVARCHAR(MAX)
);

---

⚙️ 2. Stored Procedures

✅ Insert a New Ticket

CREATE PROCEDURE usp_InsertSupportTicket
    @Title NVARCHAR(200),
    @Description NVARCHAR(MAX),
    @CategoryId INT,
    @PriorityId INT,
    @CustomerId INT,
    @CreatedBy NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @TicketId INT;

    BEGIN TRY
        BEGIN TRANSACTION;

        INSERT INTO SupportTickets (
            Title, Description, StatusId, CategoryId, PriorityId,
            CustomerId, CreatedBy, CreatedDate
        )
        VALUES (
            @Title, @Description, 1, -- Default to 'Open'
            @CategoryId, @PriorityId,
            @CustomerId, @CreatedBy, GETDATE()
        );

        SET @TicketId = SCOPE_IDENTITY();

        INSERT INTO TicketAuditLog (TicketId, Action, PerformedBy, Notes)
        VALUES (@TicketId, 'Created', @CreatedBy, 'Ticket created.');

        COMMIT;
    END TRY
    BEGIN CATCH
        ROLLBACK;
        THROW;
    END CATCH
END

---

✏️ Update Ticket Info (without changing status)

CREATE PROCEDURE usp_UpdateSupportTicket
    @TicketId INT,
    @Title NVARCHAR(200),
    @Description NVARCHAR(MAX),
    @CategoryId INT,
    @PriorityId INT,
    @UpdatedBy NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE SupportTickets
        SET Title = @Title,
            Description = @Description,
            CategoryId = @CategoryId,
            PriorityId = @PriorityId,
            UpdatedBy = @UpdatedBy,
            UpdatedDate = GETDATE()
        WHERE TicketId = @TicketId AND IsDeleted = 0;

        INSERT INTO TicketAuditLog (TicketId, Action, PerformedBy, Notes)
        VALUES (@TicketId, 'Updated', @UpdatedBy, 'Ticket details updated.');

        COMMIT;
    END TRY
    BEGIN CATCH
        ROLLBACK;
        THROW;
    END CATCH
END

---

🔄 Change Status (Assign, Close, Reopen)

CREATE PROCEDURE usp_ChangeTicketStatus
    @TicketId INT,
    @NewStatusId INT,
    @AssignedTo INT = NULL,
    @PerformedBy NVARCHAR(50),
    @Notes NVARCHAR(MAX) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE SupportTickets
        SET StatusId = @NewStatusId,
            AssignedTo = @AssignedTo,
            UpdatedBy = @PerformedBy,
            UpdatedDate = GETDATE()
        WHERE TicketId = @TicketId AND IsDeleted = 0;

        INSERT INTO TicketAuditLog (TicketId, Action, PerformedBy, Notes)
        VALUES (@TicketId, 'Status Changed', @PerformedBy, @Notes);

        COMMIT;
    END TRY
    BEGIN CATCH
        ROLLBACK;
        THROW;
    END CATCH
END

---

🔍 Search Tickets with Filters

CREATE PROCEDURE usp_SearchSupportTickets
    @CustomerId INT = NULL,
    @AssignedTo INT = NULL,
    @StatusId INT = NULL,
    @CategoryId INT = NULL,
    @FromDate DATETIME = NULL,
    @ToDate DATETIME = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT T.TicketId, T.Title, T.StatusId, S.Name AS StatusName,
           T.CategoryId, C.Name AS CategoryName,
           T.PriorityId, P.Name AS PriorityName,
           T.CreatedDate, T.AssignedTo, T.CustomerId
    FROM SupportTickets T
    JOIN Statuses S ON T.StatusId = S.StatusId
    JOIN Categories C ON T.CategoryId = C.CategoryId
    JOIN Priorities P ON T.PriorityId = P.PriorityId
    WHERE
        (@CustomerId IS NULL OR T.CustomerId = @CustomerId) AND
        (@AssignedTo IS NULL OR T.AssignedTo = @AssignedTo) AND
        (@StatusId IS NULL OR T.StatusId = @StatusId) AND
        (@CategoryId IS NULL OR T.CategoryId = @CategoryId) AND
        (@FromDate IS NULL OR T.CreatedDate >= @FromDate) AND
        (@ToDate IS NULL OR T.CreatedDate <= @ToDate) AND
        T.IsDeleted = 0
    ORDER BY T.CreatedDate DESC;
END

---

📄 Paged Ticket List

CREATE PROCEDURE usp_GetPagedTickets
    @PageNumber INT = 1,
    @PageSize INT = 10
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Offset INT = (@PageNumber - 1) * @PageSize;

    SELECT T.TicketId, T.Title, T.CreatedDate, U.UserName AS AssignedTo
    FROM SupportTickets T
    LEFT JOIN Users U ON T.AssignedTo = U.UserId
    WHERE T.IsDeleted = 0
    ORDER BY T.CreatedDate DESC
    OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;
END

---