Stored Procedure output to a table?

• rschaeferhig

  SSCrazy Eights

  Points: 8776

  More actions

  May 2, 2008 at 10:36 am

  #75201

  I have a stored procedure I want to execute and have the results in a table so I can process it. I've searched and found examples using OPENQUERY, but I don't know how to specify the "linked server" when it is, in fact, the server on which the database resides. I tried the instance name ("sqlphtsc\htsc") and it didn't like the "\". I tried the root of the instance ("sqlphtsc") and it said it couldn't find it.

  Am I heading down the right track with OPENQUERY? If so, how do I specify the server name?

  Is there another way to get the output of a Stored Procedure into a table?

   
  
  

• Joel Ewald

  SSCertifiable

  Points: 6120

  More actions

  May 2, 2008 at 12:14 pm

  #810275

  Create your temp table such that it matches your procedure results and then you can fill it.

  This does not work multiple levels deep however.

  [Code]

  CREATE PROCEDURE TestSelect

  AS

  SET NOCOUNT ON

  SELECT 1 as RecId, 'Bob' as Name

  UNION ALL

  SELECT 2 as RecId, 'Sam' as Name

  UNION ALL

  SELECT 3 as RecId, 'Joe' as Name

  UNION ALL

  SELECT 4 as RecId, 'Sue' as Name

  GO

  CREATE TABLE #Temp ( RecId int NOT NULL, FirstName varchar(15) )

  INSERT INTO #Temp

  EXEC TestSelect

  SELECT * FROM #Temp

  [/Code]

• rschaeferhig

  SSCrazy Eights

  Points: 8776

  More actions

  May 2, 2008 at 12:56 pm

  #810301

  Thanks