Parameterized SP in WHERE Clause of Another SP

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

PS: I accidentally crossposted this in another group
(http://tinyurl.com/gksq4) thinking it was this one. Sorry for that.jeremygetsmail@.gmail.com (jeremygetsmail@.gmail.com) writes:
> 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?

First of all, if your code is as simple as that, it may not even
be worth the effort. Code reuse in all its glory, but it is not always
the best in a database. That is not to say that code reuse is not
a virtue in SQL at all, but just a little smaller virtue.

Anyway, this article of mine may give you some ideas:
http://www.sommarskog.se/share_data.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx