Not sure if I fully understand the situation but one option is to use functions instead of stored procedure. Creating individual functions to fetch the values would help you to return data that can be used in a condition.
Consider the following example
(
however, keep in mind that functions used in a condition typically have a negative performance impact if the amount of data is large)
create table t1 (
id int,
val int
);
insert into t1 values
(11,11),
(12,12),
(13,13);
create table t2 (
id1 int,
id2 int,
val int
);
insert into t2 values
(11,21,1),
(12,22,2),
(13,23,3);
create table t3 (
id int,
val int
);
insert into t3 values
(1,1),
(1,2),
(2,1),
(2,2),
(3,1),
(3,2);
create function f1 (@id int) returns int as
begin
declare @retvalue int;
select @retvalue = t1.val from t1 where t1.id = @id;
return (@retvalue);
end;
select dbo.f1(11);
create function f2 (@id1 int, @id2 int) returns int as
begin
declare @retvalue int;
select @retvalue = t2.val from t2 where t2.id1 = @id1 and t2.id2 = @id2;
return (@retvalue);
end;
select dbo.f2(dbo.f1(11), 21);
select * from t3 where t3.id = dbo.f2(dbo.f1(11), 21);