How to Query on Table Returned by Stored Procedure Within a Procedure

How to query on table returned by Stored procedure within a procedure

Edit This wouldn't be suitable for 4 tables but you say in the comments you can convert to return one.

You can use OPENROWSET for this (Doesn't appear to be mentioned in Paddy's linked question). This requires adhoc distributed queries enabled.

Example usage

SELECT  *
FROM    OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who')
AS tbl

How to execute stored procedures and use their returned table within another procedure?

You have a couple options:

1) Execute your stored procedure into a temp table and then join with the temp table

CREATE TABLE #tempTable
(
   COL1 INT,
   COL2 INT   
)

INSERT INTO #tempTable 
Exec myStoredProcedure

2) Create a user defined function that returns a table and join on that.

CREATE FUNCTION output
(  

)
RETURNS TABLE 
AS
RETURN 
  SELECT Outputs.ProductId, Count(Outputs.ProductId) AS NumberOfOutputs
  FROM [dbo.PreconfiguredConfigs].[Outputs]
  GROUP BY Outputs.ProductId
GO

SQL server stored procedure return a table

A procedure can't return a table as such. However you can select from a table in a procedure and direct it into a table (or table variable) like this:

create procedure p_x
as
begin
declare @t table(col1 varchar(10), col2 float, col3 float, col4 float)
insert @t values('a', 1,1,1)
insert @t values('b', 2,2,2)

select * from @t
end
go

declare @t table(col1 varchar(10), col2 float, col3 float, col4 float)
insert @t
exec p_x

select * from @t

How to SELECT FROM stored procedure

You can use a User-defined function or a view instead of a procedure.

A procedure can return multiple result sets, each with its own schema. It's not suitable for using in a SELECT statement.

Execute a stored procedure from within another stored procedure's SELECT statement?

The approach what you have tried is invalid. Instead of the X as the stored procedure convert it as user-defined function. like the below

Create function dbo.fnGetTypeDetail
(
 @type varchar(50)
)
returns varchar(100)
As
Begin 
   return --do your operation;
End

And replace your query as:

SELECT name, type, dbo.fnGetTypeDetail(type) AS TypeDetail
FROM table

For sample, I created a scalar function. Based on your requirement you can create inline table valued function as per the example

Return query from stored procedure

You cannot do this because type of lcQuery (i.e. text or varchar) differs from the declared return type of the function. Use raise notice instead, execute the function in psql and you will see generated notice:

...
raise notice '%', lcQuery;
...

Another option is to use a temporary table with a text column, insert value of lcQuery into the table inside the function and select the table after the function was executed. You can also use a custom configuration parameter in the analogous way.

How to get return value from stored procedure with dynamic SQL query

You don't really need a cursor for this anyway, you could generate the SQL and execute it in one shot dynamically.

But in any case, it's far better to just query the system views for rowcounts

SELECT
  SchemaName = SCHEMA_NAME(t.schema_id),
  TableName = t.name,
  TotalRowCount = (
      SELECT SUM(p.rows)
      FROM sys.partitions p
      WHERE t.object_id = p.object_id
        AND p.index_id IN ( 0, 1 )  -- heap or clustered
  ),
  TotalColumns = (
      SELECT COUNT(*)
      FROM sys.columns c
      WHERE t.object_id = c.object_id
  )
FROM sys.tables t;

---

Related Topics