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
