Spaces:
Sleeping
Sleeping
File size: 2,282 Bytes
f53fbd9 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 | :setvar DemoDatabase "CustomerAIDemo"
:setvar EmbeddingModelName "LocalEmbeddingModel"
USE [$(DemoDatabase)];
GO
CREATE OR ALTER PROCEDURE dbo.usp_LegacyKeywordSearch
@Keyword NVARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP (20)
FeedbackId,
Product,
CustomerSegment,
RiskLevel,
Channel,
CreatedAt,
FeedbackText
FROM dbo.CustomerFeedback
WHERE FeedbackText LIKE N'%' + @Keyword + N'%'
ORDER BY CreatedAt DESC;
END
GO
CREATE OR ALTER PROCEDURE dbo.usp_SemanticRiskSearch
@Question NVARCHAR(MAX),
@CustomerSegment NVARCHAR(50) = NULL,
@RiskLevel NVARCHAR(20) = NULL,
@DaysBack INT = 30
AS
BEGIN
SET NOCOUNT ON;
DECLARE @query VECTOR(1024) =
AI_GENERATE_EMBEDDINGS(@Question USE MODEL $(EmbeddingModelName));
SELECT TOP (20) WITH APPROXIMATE
f.FeedbackId,
f.Product,
f.CustomerSegment,
f.RiskLevel,
f.Channel,
f.CreatedAt,
f.FeedbackText,
r.distance
FROM VECTOR_SEARCH(
TABLE = dbo.CustomerFeedback AS f,
COLUMN = Embedding,
SIMILAR_TO = @query,
METRIC = 'cosine'
) AS r
WHERE (@CustomerSegment IS NULL OR f.CustomerSegment = @CustomerSegment)
AND (@RiskLevel IS NULL OR f.RiskLevel = @RiskLevel)
AND (@DaysBack IS NULL OR f.CreatedAt >= DATEADD(DAY, -@DaysBack, SYSUTCDATETIME()))
ORDER BY r.distance;
END
GO
CREATE OR ALTER PROCEDURE dbo.usp_FindSimilarCases
@FeedbackId INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @caseVector VECTOR(1024);
SELECT @caseVector = Embedding
FROM dbo.CustomerFeedback
WHERE FeedbackId = @FeedbackId;
IF @caseVector IS NULL
BEGIN
THROW 51001, 'FeedbackId not found or embedding is null.', 1;
END
SELECT TOP (25) WITH APPROXIMATE
f.FeedbackId,
f.Product,
f.CustomerSegment,
f.RiskLevel,
f.CreatedAt,
f.FeedbackText,
r.distance
FROM VECTOR_SEARCH(
TABLE = dbo.CustomerFeedback AS f,
COLUMN = Embedding,
SIMILAR_TO = @caseVector,
METRIC = 'cosine'
) AS r
WHERE f.FeedbackId <> @FeedbackId
ORDER BY r.distance;
END
GO
|