Posted by Erland Sommarskog on 09/30/90 11:49

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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: How to Insert date in sql server database
• Prev in forum: Re: Question about returning a smalldatetime from a Function
• Thread view: Re: Parameterized SP in WHERE Clause of Another SP

[Reply to this message]