By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,892 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

Parameterized SP in WHERE Clause of Another SP

P: n/a
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.

Jun 6 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
je************@gmail.com (je************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 6 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.