Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    7

    Red face Unanswered: SELECT from a Stored Procedure

    Hi everybody,
    How can I select records from a SP?
    For example:
    My SP is the following:

    CREATE PROCEDURE [dbo].[spExample]
    AS
    Declare @SELECT_Text AS varchar(500)
    Set @SELECT_Text='SELECT * FROM dbo.ExampleTable'
    exec(@SQL_Text)
    GO

    I want to select from it:

    SELECT *
    FROM dbo.spExample

    It doesn't work. Then how can I use the result of the SP???

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    You could use temporary table:

    create table #tmp(...)

    insert #tmp
    exec spExample

    select * from #tmp
    Last edited by snail; 01-30-04 at 10:46.

  3. #3
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Make it a function:

    CREATE FUNCTION [dbo].[fnExample]
    RETURNS TABLE
    AS
    RETURN SELECT * FROM dbo.ExampleTable
    GO

    then you can use:

    SELECT * FROM dbo.fnExample

    ---------------------------------------------------------------------


    As a side note, in the sproc code you listed, you would be better off with:

    CREATE PROCEDURE [dbo].[spExample]
    AS
    SELECT * FROM dbo.ExampleTable
    GO
    -bpd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •