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).
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"jeremyNYC" <jeremygetsmail[ at ]gmail.com> wrote in message
news:1149584221.876336.204830[ at ]i40g2000cwc.googlegroups.com...
[Quoted Text] > I've got this SP:
> CREATE PROCEDURE
> EWF_spCustom_AddProfiles_CompanyYear
> [ at ]prmSchoolYear char(11)
> AS
> SELECT
> ContactID
> FROM
> dbo.EWF_tblCustom_CompanyProfile
> WHERE
> SchoolYear = [ at ]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
> [ at ]prmSchoolYear2 char(11)
> AS
> SELECT
> ContactID, SomeOtherFields
> FROM
> tblContact
> WHERE
> ContactID IN (exec EWF_spCustom_AddProfiles_CompanyYear
> [ at ]prmSchoolYear2)
>
> How would I make that happen?
>
> If this isn't possible, what else might I try?
>
> Thanks much for any pointers.
>
> Jeremy
>
|