CODEWITHSUNDEEP
sqlsql-serverstored-procedures
user815460
user815460

Reputation: 1143

SQL Server stored procedure using a temp table field as the where clause for a select

I have a single column temp table #colors in a stored procedure. The column is called color. I want to query another table tblMain with a SELECT statement like this:

SELECT userid
FROM tblMain
WHERE color = 'red' OR color = 'white' or color = 'blue'

but I don't want the WHERE clause to be static (as in the example above), I want it to use the color field from the #colors table to form the WHERE clause.

Is this even possible ?

Thanks!

Upvotes: 0

Views: 90

Answers (2)

Ashokreddy
Ashokreddy

Reputation: 352

you can use like this

SELECT userid FROM tblMain 
WHERE color IN (SELECT distinct color FROM #colors)

Upvotes: 1

GolezTrol
GolezTrol

Reputation: 116110

You can use a select in an in clause:

SELECT userid FROM tblMain 
WHERE color IN (SELECT color FROM colors)

or just an (inner) join:

SELECT userid FROM tblMain 
INNER JOIN colors ON colors.color = tblMain.color

Upvotes: 3

Related Questions