How to select a comma separated list and use it in WHERE IN clause

First create a user-defined function that retrieves a comma separated list of IDs from one table using COALESCE built-in function, sort of like I did here

How to combine several rows in one row, separated by commas

CREATE FUNCTION [dbo].[GetListOfIDs] ( )
RETURNS VARCHAR(8000)
AS
BEGIN
  DECLARE @RES VARCHAR(8000)
  SELECT @RES = COALESCE(@RES + ',','') + ID
  FROM Table1
  RETURN (@RES)
END

Then create a stored procedure that will retrieve all the data from another table, based on input list:

CREATE PROCEDURE [dbo].[usp_GetAllData]
@list varchar(500)
as
begin
declare @SQL varchar(600)
set @SQL = 'SELECT * FROM TABLE2 WHERE ID IN ('+ @list +')'
EXEC(@SQL)
end       

Then you will need one more function to tie it all together - use user-defined function to retrieve the list of IDs and then execute stored procedure and pass it the list:

CREATE PROCEDURE usp_RunProcedure
AS
BEGIN
DECLARE @ids varchar(600)  
SELECT @ids = dbo.GetListOfIDs()

EXEC dbo.usp_GetAllData @list=@ids
END