CODEWITHSUNDEEP
sqlsql-servert-sql
Diogo Santos
Diogo Santos

Reputation: 830

LIKE clause applied to a DB Type in Stored Procedure

I have created the following Type:

CREATE TYPE dbo.expressions
AS TABLE
(
  expression varchar(255)
);
GO

How can I, in a Stored Procedure, use the n types that I receive and do, with each one, an select with a like clause?

In terms of "programming" (and pseudo-code), would be something like this:

for each expression in expressions
rows+=select * from table where table.field LIKE '%' + expression[i] + '%'

I can always call the SP multiple times from my API but I was wondering if this is possible, and even faster, to do in SQL side.

Upvotes: 0

Views: 41

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272306

You simply SELECT from / JOIN with an instance of the type. Assuming you have something like:

CREATE TYPE expressions AS TABLE (expression varchar(255));
CREATE PROCEDURE mysp(@expressions AS expressions) ...

You can use the variable like you would use a table:

SELECT * 
FROM @expressions AS expressions
INNER JOIN yourtable ON yourtable.field LIKE '%' + expressions.expression + '%'

The above will allow you to use expression in select clause. Otherwise you can use the following:

SELECT * 
FROM yourtable
WHERE EXISTS (
    SELECT 1
    FROM @expressions AS expressions
    WHERE yourtable.field LIKE '%' + expressions.expression + '%'
)

Upvotes: 5

Related Questions