Re: Stored Procedure B Selects Some Columns From Stored Procedure A

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 09/24/04 

Date: Fri, 24 Sep 2004 08:57:59 -0500

Like Tibor mentioned, you can encapsulate the SP_A query in a view.and then
use the view directly in SP_B. You don't need parameters in the view
because you can specify a WHERE clause for ad-hoc queries:

CREATE VIEW View_A AS
SELECT A, B, C, D, E, ComputerId
FROM MyTable t1
JOIN MyTable2 t2 ON
    t1.MyKey = t2.MyKey
JOIN MyTable3 t3 ON
    t1.MyKey = t3.MyKey
GO

CREATE PROCEDURE SP_B
AS
SELECT A, C, E
FROM View_A
WHERE ComputerId = 'SMITH'
GO 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Robichaud" <robert.robichaud@sympaticoREMOVE4SPAM.ca> wrote in 
message news:OsZ8dIfoEHA.1800@TK2MSFTNGP15.phx.gbl...
> Well SP_A is the result of 6 tables joined together.  And I just realized
> that SP_B will have 2 other tables joined with it to produce the desired
> results.
>
> If I use a view, how would I pass a parameter through to the Stored
> Procdure?
>
>
> "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
> news:%23Q0wj$eoEHA.3488@TK2MSFTNGP12.phx.gbl...
>> The only other method I can think of is OPENROWSET, but that really isn't
>> intended for this sort of thing.  If SP_A isn't too complex, you also
> might
>> consider a view instead of a proc.
>>
>> -- 
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>>
>> "Robert Robichaud" <robert.robichaud@sympaticoREMOVE4SPAM.ca> wrote in
>> message news:uARQN1eoEHA.1816@TK2MSFTNGP09.phx.gbl...
>> > Thank you for your example.
>> >
>> > What I need is to use SP_B to filter and use selected columns from 
>> > SP_A.
>> > SP_A will be used in other stored procedures.  SP_A has 37 columns: I
> just
>> > simplified it for this posting.
>> >
>> > I'd like to avoid using a temporary table but if I have too, so be it.
> Is
>> > there an alternative?
>> >
>> > Thanks.
>> >
>> > "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
>> > news:%23SgKYweoEHA.3396@tk2msftngp13.phx.gbl...
>> >> You can use INSERT ... EXEC.  Below is an untested example:
>> >>
>> >> CREATE PROC SP_B
>> >> AS
>> >> CREATE TABLE #SP_A_Results
>> >> (
>> >>     A int,
>> >>     B int,
>> >>     C int,
>> >>     D int,
>> >>     E int
>> >> )
>> >>
>> >> INSERT INTO #SP_A_Results
>> >> EXEC SP_A @ComputerID = 'SMITH'
>> >>
>> >> SELECT A, C, E
>> >> FROM #SP_A_Results
>> >>
>> >> DROP TABLE #SP_A_Results
>> >> GO
>> >>
>> >> -- 
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >>
>> >> "Robert Robichaud" <robert.robichaud@sympaticoREMOVE4SPAM.ca> wrote in
>> >> message news:uyB3SmeoEHA.1088@TK2MSFTNGP09.phx.gbl...
>> >> > I've been trying to solve this for a couple of days but have not
> found
>> > an
>> >> > answer.
>> >> >
>> >> > In Access 2000, I would like to call Stored Procdure SP_B, that
> returns
>> >> > Columns A, C and E generated from Store Procedure SP_A (requires a
>> >> > parameter
>> >> > like @ComputerID='SMITH') that creates columns A, B, C, D, E, F.
>> >> >
>> >> > Any examples or links to articles would be greatly appreciated.
>> >> >
>> >> > Thanking you in advance,
>> >> >
>> >> > Robert
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

Relevant Pages