Parameterized SP in WHERE Clause of Another SP

[jeremyNYC]

I've got this SP:
CREATE PROCEDURE
EWF_spCustom_AddProfiles_CompanyYear
@prmSchoolYear char(11)
AS
SELECT
ContactID
FROM
dbo.EWF_tblCustom_CompanyProfile
WHERE
SchoolYear = @prmSchoolYear

I'd like to be able to reference that in the where clause of another
SP. Is that possible?

I'd like to end up with something like this:
CREATE PROCEDURE
MyNewProc
@prmSchoolYear2 char(11)
AS
SELECT
ContactID, SomeOtherFields
FROM
tblContact
WHERE
ContactID IN (exec EWF_spCustom_AddProfiles_CompanyYear
@prmSchoolYear2)

How would I make that happen?

If this isn't possible, what else might I try?

Thanks much for any pointers.

Jeremy

 

[Sylvain Lafontaine]

You should ask this type of question in m.p.sqlserver.programming.

The usual way of doing this is to create a self-referenced linked server and
use an OPENQUERY. You can also use an OPENROWSET. Another possibility
would be to use a temporary table (or a local variable table) to transmit
data between these two procedures. You can also EXEC your statement to
append your data directly into

However, in your case, you should strongly consider the possibility of using
a function instead of a stored procedure. (Unless if you are on a SQL-Server
7 or less, of course).

 

[Vadim Rapp]

Hello jeremyNYC:

j> I'd like to be able to reference <stored procedure> in the where clause
of another
j> SP. Is that possible?

No.

j> How would I make that happen?

copy and paste the select from the 1st sp inside the parenthesis in the 2nd
one, and adjust parameter name.


Vadim Rapp