Declare @dataTable table (colA varchar(20))
Insert Into @dataTable
Select 'Corey Aldebol'
Union Select 'Seventhnight'
Union Select 'Kaiden Elizabeth'
Union Select 'Julie Aldebol'


Declare @criteriaTable table (criteria varchar(20))
Insert Into @criteriaTable
Select '%Aldebol'
Union Select '%Liz%'

Select * 
From @dataTable A
Inner Join @criteriaTable B
On A.colA like B.criteria

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(NAME_CLIENTE varchar(20))
GO

INSERT INTO myTable99(NAME_CLIENTE)
SELECT 'AjarnMark' UNION ALL SELECT 'Byrmol' UNION ALL SELECT 'Chadmat' UNION ALL SELECT 'DougG' UNION ALL 
SELECT 'Ehorn' UNION ALL SELECT 'Frank Kalis' UNION ALL SELECT 'Graz' UNION ALL SELECT 'Harshal_in' UNION ALL
SELECT 'izaltsman' UNION ALL SELECT 'jsmith8858' UNION ALL SELECT 'Kristen' UNION ALL SELECT 'Lumbago' UNION ALL 
SELECT 'Merkin' UNION ALL SELECT 'Nr' UNION ALL SELECT 'Onamuji' UNION ALL SELECT 'Page47' UNION ALL 
SELECT 'Quazibubble' UNION ALL SELECT 'rrb' UNION ALL SELECT 'SamC' UNION ALL SELECT 'tduggan' UNION ALL SELECT 'Utpal' UNION ALL 
SELECT 'ValterBorges' UNION ALL SELECT 'Wanderer' UNION ALL SELECT 'x002548' UNION ALL SELECT 'yakoo' UNION ALL SELECT 'zippy'

SELECT * FROM myTable99

DECLARE @x table(Col1 varchar(20))

INSERT INTO @x(Col1) SELECT 'j' UNION ALL SELECT 'c' UNION ALL SELECT 't'

SELECT * FROM @x

SELECT * FROM myTable99 m INNER JOIN @x x ON m.NAME_CLIENTE LIKE x.Col1 + '%'
GO 

SET NOCOUNT ON
DROP TABLE myTable99
GO

quote:

---

See...you should have never said never...or no way

---

select tbl.*
from tbl
join (Select s=data From dbo.Split('a%,%b%,%c',',')) a
on tbl.fld like a.s

quote:

---

Originally posted by X002548

Well aren't table variables stored in memory?

And why would Nigel's method use tempdb?

And wouldn't you have to use dynamic sql if the number of criteria or the criteria itself changes?



Brett

8-)

---

quote:

---

Originally posted by chadmat

No, Table Variables always use TempDB, they really are no different that temp table

---

quote:

---

One reason the table data type is so much faster is that it doesn't occupy space in the tempdb database as it's temporary table counterpart. Instead, the table data type uses the SQL Server's memory to store the data

---

quote:

---

Originally posted by chadmat

Let me clarify, in the same way a temp table works, if the table will fit in memory, it won't necissarily use TempDB, but it is no different than a Temp Table in it's memory/tempdb behavior.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

---

quote:

---

Originally posted by X002548

quote:

---

Originally posted by chadmat

No, Table Variables always use TempDB, they really are no different that temp table

---

Huh?

Not that it references it in BOL, but Brian Knight does

quote:

---

One reason the table data type is so much faster is that it doesn't occupy space in the tempdb database as it's temporary table counterpart. Instead, the table data type uses the SQL Server's memory to store the data

---

Brett

8-)

---

quote:

---

Q4: Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables, because they are maintained in a database that resides on the physical disk?

A4: A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).

---

quote:

---

OK, but doesn't a temp table always go to te,pdb?

---