Stored procedure to select from multiple tables

Question

I have created a stored procedure that takes an argument

ALTER procedure [dbo].[myprocedure](@myName char(20))
as
   select param1
   from table1
   where name = @myName

Now this is working but when I try to select parameters from other tables it is not working I tried like this after the first select

(Select param2 table2)

But I get

MS 512, Level 16, State 1, Procedure candidate, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Does anyone know hot to use the select to get values from more than one table? Thanks

Does that mean you want Param1 from table1 and Param2 from table 2? In that case use Union all — Nilesh
– Nilesh, Commented Jan 4, 2014 at 9:46

Mudassir Hasan · Accepted Answer · 2014-01-04 09:46:48Z

3

Use IN operator instead of = after Where clause

ALTER procedure [dbo].[myprocedure](@myName char(20))
as

select param1
from table1
where name = @myName

---
select param1
from table1
where name IN (Select param2 from table2)

Edit

select param1
from table1
where name = @myName
union
Select param2
from table2

Note that UNION is going to remove duplicate values after combining resultset from table1 and table2.

To keep duplicate values , use UNION ALL

edited Jan 4, 2014 at 9:46

answered Jan 4, 2014 at 9:31

Mudassir Hasan

29k21 gold badges104 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3005313 Over a year ago

Yeah but how can I match the name with @myName

Mudassir Hasan Over a year ago

So you want to match name column from table1 with param2 column of table2 as well as with paramaeter @myName ?

user3005313 Over a year ago

I want to match name colum from table1 with @myName and to return param2 from table 2

Collectives™ on Stack Overflow

Stored procedure to select from multiple tables

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related