RE: Stored Procedure as part of SELECT?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Ben (Ben_at_discussions.microsoft.com)
Date: 12/06/04 

Date: Mon, 6 Dec 2004 14:25:03 -0800

Joe,

I have found that using an UDF works really nice some times in place of a
view or a stored procedure. The UDF allows me to provide parameters and still
be able to join the results as in a view or table. This method allows me to
encapsulate the criteria for the query.

If your stored procedure is not too complicated take a look at turning it
into a view.

I have some situations where the queries are soooo complicated that I have
used multiple UDFs to break it up just so that I did not (I was not able to)
use a stored procedure.

Be careful with UDFs though...there are a lot of notes regarding performance
metrics not being reliable for queries that use an UDF. Your final option
that has not already been provided would be to turn the stored procedure into
a view. This often can not be done.

Cheers,

Ben

"Joe Williams" wrote:

> 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
>
>
>

Relevant Pages

  • Re: Stored Procedure as part of SELECT?
    ... INSERT -- EXEC to get the result set from the first sp, ... How do I create a new Stored Procedure that puts information from ... > vw_Employee is a view and has fields like (EmployeeID, FirstName, ... > contains (EmployeeID, Hours, FirstName, LastName, DepartmentId, ...
    (microsoft.public.sqlserver.programming)
  • Using stored procedure result set in another stored procedure
    ... I've been developing a stored procedure that uses a user defined ... function in the query portion of the procedure. ... product needs to allow for dynamic table names, the UDF will not work. ... Line 4: Incorrect syntax near '0'. ...
    (comp.databases.ms-sqlserver)
  • Re: Interview Questions Feb 01 2006
    ... > What's the difference between Stored Procedure and User Defined ... in ORACLE the UDF can have OUT parameters. ... > SP's can change some of the server environment. ...
    (comp.programming)
  • Re: Stored Procedure as part of SELECT?
    ... error: Error 198: EXECUTE cannotbe used as a source when inserting into a ... what I am trying to do is to pass a stored procedure the end date ... >> contains (EmployeeID, Hours, FirstName, LastName, DepartmentId, ... >> all would have EmployeeID in common. ...
    (microsoft.public.sqlserver.programming)
  • Re: VIEW showing result of a STORED PROCEDURE ?
    ... There are similarities and differences between UDF and SP. ... from (select distinct EmployeeID from Orders)derived ... Can you create a user function and leave it on> the system like a stored procedure to be used within views or is it more> like a temporary object, like a temporary table, functions are always> temporary? ... >> create proc usp ...
    (microsoft.public.sqlserver.programming)