Re: Stored Procedure as part of SELECT?
From: Joe Williams (JOE_at_ANYWHERE.com)
Date: 12/07/04
- Next message: Ben: "RE: Select * From SQL"
- Previous message: Steve Kass: "Re: Query HELP!!!"
- In reply to: Mike John: "Re: Stored Procedure as part of SELECT?"
- Next in thread: David Gugick: "Re: Stored Procedure as part of SELECT?"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 7 Dec 2004 09:40:49 -0500
Mike,
Thanks. Can you provide a simple code example of how I would decalre the
temp table variable and then execute a stored procedure that would populate
the temp table variable? Keep in mind I have a "master" store procedure that
needs to use the results from the temp table, which would be populated by
the "sub" stored procedure.
"Mike John" <Mike.John@knowledgepool.com> wrote in message
news:e2sARWC3EHA.2012@TK2MSFTNGP15.phx.gbl...
> Sorry - I never realised you could not use that with table variables - it
> sounds like you will need to make it a temp table then. (prefix name with
> #)
>
> Mike John
>
> "Joe Williams" <JOE@ANYWHERE.com> wrote in message
> news:eyjq7c%232EHA.1192@tk2msftngp13.phx.gbl...
>> hi mike
>>
>> When I try to do the INSERT -- EXEC as you suggested, I get teh following
>> error: Error 198: EXECUTE cannotbe used as a source when inserting into a
>> table variable." Here is my code:
>> Agaion, what I am trying to do is to pass a stored procedure the end date
>> and then use the results to join to another view.
>>
>> CREATE PROCEDURE spToolingWIP(@EndDate datetime)
>> AS
>> declare @tmpReceipts table(JobNumber varchar(30), ActMOL decimal)
>>
>> INSERT INTO @tmpReceipts
>> Execute spToolingWIPActReceipts @enddate
>>
>> SELECT JobNumber, ActMOL, TotalHours
>> FROM tmpActReceipts INNER JOIN JobHours on tmpactreceipts.jobnumber =
>> jobHours.jobnumber
>>
>> GO
>>
>> Thanks
>>
>> Joe
>> "Mike John" <Mike.John@knowledgepool.com> wrote in message
>> news:uWphF592EHA.4004@tk2msftngp13.phx.gbl...
>>> You could define a table variable in your new sp and populate it via
>>> INSERT -- EXEC to get the result set from the first sp, then simply join
>>> that to the views as required to return the final result.
>>>
>>> It sounds like the sp sp_EmployeeHours is a single select statement so
>>> maybe it could be a view?
>>>
>>> I would also suggest you revise your naming conventions - using sp_ as a
>>> prefix on stored procs has a number of implications that you will find
>>> loads of references to if you search the history in this group.
>>> Similarly vw_ is not in practice helpful.
>>>
>>> Mike John
>>>
>>>
>>> "Joe Williams" <JOE@ANYWHERE.com> wrote in message
>>> news:%23C355y92EHA.824@TK2MSFTNGP11.phx.gbl...
>>>>I have a Store Procedure that returns records based on parameters that I
>>>>send it (date range). Then I also have two different views that return
>>>>records. How do I create a new Stored Procedure that puts information
>>>>from all three objects into one recordset?
>>>>
>>>> Example
>>>>
>>>> sp_EmployeeHours is a stored procedure that has two fields (EmployeeID,
>>>> Sum(Hours) as WorkTotal) and the results are limited to data range
>>>> critiera that I send to the stored procedure.
>>>>
>>>> vw_Employee is a view and has fields like (EmployeeID, FirstName,
>>>> LastName)
>>>> vw_EmployeePurchases is a view and has fields like (EmployeeID,
>>>> Sum(Purchases) as PurchTotal)
>>>>
>>>> I would like to create a new SP called SP_main that I can use to pass
>>>> the desired date range into sp_EmployeeHours and then return a SELECT
>>>> that contains (EmployeeID, Hours, FirstName, LastName, DepartmentId,
>>>> DepartmentDesc) Basically the results of the SP and both the views.
>>>> Thay all would have EmployeeID in common.
>>>>
>>>> What do you think? Thanks
>>>>
>>>> joe
>>>>
>>>
>>>
>>
>>
>
>
- Next message: Ben: "RE: Select * From SQL"
- Previous message: Steve Kass: "Re: Query HELP!!!"
- In reply to: Mike John: "Re: Stored Procedure as part of SELECT?"
- Next in thread: David Gugick: "Re: Stored Procedure as part of SELECT?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
