stored procedure help

In asp this can be done .......

Function getName(ID)
Select Name from User where UserId = ID
return Name
End Function

Select ID from IDtable
<%=getName(ID)%>

how can i do this in stored procedure????

create CREATE PROCEDURE MYSP @ID integer, @Name varchar(50) OUTPUT
as

Select @Name= name from User where UserId = @ID
return Name

To execute it:
DECLARE @Name varchar(50)
EXECUTE MYSP 2, @Name OUTPUT
--2 being the @id

If @Name is ='your'
begin
....
end

the table looks like this

--------------------
id (id table)
--------------------
1
2
3
4
---------------------


---------------------
id | Name (Name table)
---------------------
1 | rec1
1 | rec2
3 | rec3
4 | rec4
---------------------

then who do i get all the names out that reference to the same id???

Check out the link to someone who's covering INNER JOINS on the main MSSQL page. - That's what you need, by the sounds of it.