We are writing a stored procedure responsible for get a stored procedure name and returning a result containing the stored procedure columns and their data types. However, we bumped into a problem executing a dynamic query to return the results of stored procedure, but we can't store it in a temp table!

You can see our query below:

  DECLARE @ProcName VARCHAR(100)='spGetOraganizationsList',
  @ParamName VARCHAR(100),@DataType VARCHAR(20),
  @Query NVARCHAR(MAX)='EXEC '+'spGetOraganizationsList '

  SELECT  PARAMETER_NAME,DATA_TYPE 
  INTO #Tmp
  FROM  information_schema.PARAMETERS
  WHERE [email protected]

  DECLARE ParamCursor CURSOR 
  FOR SELECT * FROM #Tmp
  OPEN ParamCursor
  FETCH NEXT FROM ParamCursor
  INTO @ParamName,@DataType

  WHILE @@FETCH_STATUS = 0 
  BEGIN
  SET @[email protected][email protected]+'=Null,'
  FETCH NEXT FROM ParamCursor INTO @ParamName,@DataType
  END
  CLOSE ParamCursor
  DEALLOCATE ParamCursor
  DROP TABLE #Tmp

  EXEC sp_executesql @Query

The thing is I can't store the results of it in a temp table, and OPENROWSET does not accept variables.

4 Answers

-1
Sourabh Shah On

Why not create a view instead of creating and dropping the tables.

CREATE VIEW "VIEW_NAME" AS "SQL Statement";

An unrelated example:-

CREATE VIEW V_Customer
AS SELECT First_Name, Last_Name, Country
FROM Customer;
1
Abolfazl On

I think it comes from sql concept that it doesn't trust in result of stored procedures and because of that we cannot select on it or store it in a table by 'making in query table' method. Unless you create a table and define it's columns and sql trust to you and you insert result of it into this table for example take below situation

Create table test (name varchar(10),family varchar(20))


Insert into test
Exec sp-testResult

Now if you define wrong column for your table you will receive query runtime error .actually sql doesn't predict result of sp and leaves it to you to define result of your stored procedure.

0
Wayne Erfling On

You can certainly INSERT the results of a stored procedure into a TEMP table:

CREATE PROCEDURE PurgeMe
AS
SELECT convert(int, 1) AS DaData
UNION
SELECT convert(int, 2)
GO

CREATE TABLE #Doodles  (AnInteger int)

INSERT #Doodles EXECUTE PurgeMe

SELECT * FROM #Doodles

Questions arise about the SCOPE of TEMP tables, however. You might find that in your calling routine you will not be able to see a TEMP table created within your routine.

The solution to the SCOPE problem is to do the following:

  1. Create a minimal TEMP table (say, with one column)
  2. Use ALTER TABLE on the TEMP table within your routine to make its schema match your needs (this can be tricky, but it can be done)
  3. Put data into the TEMP table
  4. return from your routine - the calling routine will now be able to access the temp table

If this is of interest I can make a longer post with a stored procedure to do the above. It was written to facilitate dynamic SQL

0
Serg On

Use global temp table and dynamic OPENROWSET

  DROP TABLE ##Tmp;
  GO

  DECLARE @ProcName VARCHAR(100)='spGetOraganizationsList',
  @ParamName VARCHAR(100), @DataType VARCHAR(20),
  -- Mind to specify database and schema of the SP
  @Query NVARCHAR(MAX)=' EXEC [mydb].[dbo].spGetOraganizationsList ';
  SELECT  PARAMETER_NAME,DATA_TYPE 
  INTO #Tmp
  FROM  information_schema.PARAMETERS
  WHERE [email protected];

  -- Build SP exec

  DECLARE ParamCursor CURSOR 
  FOR SELECT * FROM #Tmp
  OPEN ParamCursor
  FETCH NEXT FROM ParamCursor
  INTO @ParamName,@DataType

  WHILE @@FETCH_STATUS = 0 
  BEGIN
     SET @[email protected][email protected]+'=Null,'
     FETCH NEXT FROM ParamCursor INTO @ParamName,@DataType
  END
  CLOSE ParamCursor
  DEALLOCATE ParamCursor
  SET @Query = left(@Query, len(@Query) - 1);

  -- Build ad hoc distributed query which creates ##Tmp from SP exec.

  SET @Query = 'SELECT * INTO ##Tmp FROM OPENROWSET(''SQLNCLI'', ''Server=localhost;Trusted_Connection=yes;'',''' + @Query + ''')';

  EXEC (@Query);

  -- Created by dynamic sql `##Tmp` is availabe in the current context. 
  SELECT *
  FROM ##Tmp;

Don't forget to enable ad hoc distributed queries first.

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO