CODEWITHSUNDEEP
sqlsql-servert-sql
umesh.chape
umesh.chape

Reputation: 3035

Pass values as parameter from select query

I want to pass values from output of select query to another query. Basically both queries will be part of a stored procedure. e.g.

select Id, RelId
from tables

There will be multiple rows returned by above query and I want to pass them to the following query

select name
from table2
where Id = @Id and MgId = @RelId

Please suggest

Upvotes: 0

Views: 2444

Answers (3)

theteague
theteague

Reputation: 413

Or...

SELECT *
INTO #Table1
FROM ...

SELECT *
INTO #Table2
FROM ...

SELECT *
FROM #Table1 T1
JOIN #Table2 T2

DROP TABLE #Table1, #Table2

Upvotes: 0

GuidoG
GuidoG

Reputation: 12059

You cannot pass multiple values in SQL.

But maybe you can just join your 2 tables, that would be far more efficient. Not knowing your table schemes I suggest something like this. You might have to adapt this to your actual table schemas off course

select name
from table2 t2
inner join tables t on t2.Id = t.Id
    and t2.MgId = t.RelId

EDIT
As Gordon mentioned in his answer, this approach can show double rows in your result.
If you don't want that than here are 2 ways of getting rid of the doubles

select distinct name
from ...

or by grouping by adding this at the end of the statement

group by name

Though this will work, avoiding the doubles like in Gordon's answer is better

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271023

I would suggest using exists:

select t2.name
from table2 t2
where exists (select 1
              from tables t
              where t2.Id = t.Id and t2.MgId = t.RelId
             );

The difference between exists and join is that this will not generate duplicates, if there are multiple matches between the tables.

Upvotes: 0

Related Questions