Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2004
    Posts
    5

    Unanswered: using a storeprocedure in a select statement

    Hi,
    i would like to use a storeprocedure as the from clause for a select statement..

    something like

    select * from exec spGetData

    which obviously doesn't work.. anybody know a way i can do this?

    thanks
    chris

  2. #2
    Join Date
    Nov 2002
    Posts
    71
    You should consider that the 'Select * from' is not necessary because your stored procedure can produce output. If your stored procedure doesn't produce output, you can't make it do so unless you amend the stored procedure.

    Clive

  3. #3
    Join Date
    Jul 2004
    Posts
    5
    hi,
    i need to have this syntax because it is going into a specialist reporting system and that needs the syntax of select feild1, field2 from ...etc

    and also it needs to use the same sp as another part of the system.. i'm sure theres a way of "selecting" fields from a dataset returned by a store procedure..

    rgds
    chris

  4. #4
    Join Date
    Nov 2002
    Posts
    71
    Well, I'm not aware of any method of selecting directly from a stored proc. Maybe there is but I haven't heard about it. What you could do is to direct the output to a temp file and then select from that...

    create table #output ( ... )

    insert into #output
    exec spGetData

    select * from #output

    drop table #output

  5. #5
    Join Date
    Jul 2004
    Posts
    5
    hi,
    yeah thats the work around i'm trying now..

    cheers for the info..

    and if i do find away todo it i'll post it here..

    rgds
    chris

  6. #6
    Join Date
    Aug 2004
    Posts
    15
    This can be done with a user defined function but not a stored procedure. Here is the example from SQL Books Online:

    CREATE FUNCTION fn_CustomerNamesInRegion
    ( @RegionParameter nvarchar(30) )
    RETURNS table
    AS
    RETURN (
    SELECT CustomerID, CompanyName
    FROM Northwind.dbo.Customers
    WHERE Region = @RegionParameter
    )
    GO

    -- Example of calling the function for a specific region
    SELECT *
    FROM fn_CustomerNamesInRegion(N'WA')
    GO

  7. #7
    Join Date
    Jul 2004
    Posts
    5
    hi,
    thanks for the reply.. thats the solution i finaly come 2 aswell..

    rgds
    chris

Posting Permissions

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