CODEWITHSUNDEEP
sqlsql-server
5unnyr4y3
5unnyr4y3

Reputation: 95

Compare String to Column in SQL Server, and Return a value

I'm not sure if I should create a View, Stored Procedure, or function for this, but here is what I have so far:

USE [HomeStorageQA2]

GO
DECLARE @SearchValue Varchar(255)
DEClare @Flag Varchar(255)
SET @Flag = NULL
SET @SearchValue = 'Inner point(s) > outer point(s);'
Declare @MainString Varchar(255)
Set @MainString = dbo.GetComments
if Charindex(@SearchValue,@MainString) > 0
begin
Set @Flag = 'True'
end
Select  'Flag' = @Flag
go

If I Set @MainString = [Some value containing SearchValue] , then the query returns True. I want to use this query for the rows in TestComments.Comments; so it's comparing the value of Comments to the @SearchValue, and returning True or False for each row.

I already have a Stored Procedure dbo.GetComments to find a match via an identifier called SummaryID in the TestComments table.

  1. My final goal is to be able to create a Select statement with three columns of data: SummaryID, Comments, and the corresponding @Flag value for all rows in TestComments. Will this syntax be able to create what I need, and how do I set @MainString to the value I want?

  2. Should this be created as a stored procedure, function, or view? (I'm assuming Stored Procedure or Function)

Upvotes: 1

Views: 1723

Answers (3)

Mark
Mark

Reputation: 182

Here's a stored procedure version:

CREATE PROCEDURE FindMatches
    @searchValue varchar(MAX)
AS
BEGIN
SELECT SummaryID
    , Comments
    , CASE
        WHEN CHARINDEX(@searchValue, Comments) > 0 THEN 'True'
        ELSE 'False'
        END AS Flag
FROM TestComments
END

Upvotes: 3

Drew
Drew

Reputation: 24959

make it a proc/fnc if and only if it rises to the level of re-usability and coveted status that it belongs there. if you don't know the difference, then the answer is no. it is probably random gibberish not worthy of such status, less you desire a system of way crazy inching toward an inevitable face-palm

Upvotes: 2

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

For example you can create a scalar function that returns bit. The it is done like:

select SummaryID, Comments, dbo.FunctionName(Comments, @searchString)  
from TestComments

Upvotes: 1

Related Questions