Is there a way to select from a results of a stored procedure in SQL Server?

I have a new stored procedure that runs a god amount of logic to determine a result set to return.

I have two custom dataset properties on a root container that select certain columns from the old result set by a direct query, but now need to select the column data from the results of the stored procedure.

I tried

Select t.[col1], t.[col2], t.[col3]
From (exec dbo.getAnalysisTestNumberDefaults '{[Client]Schema2}', {Root Container.Product}, NULL, {[Client]User/id}) as t

but it did not work. Is this possible, or will I need to go a different route?

I don’t believe this is possible with the JBDC drivers after trying a few different ways. My solution was a parameter bit to the stored procedure that determines what columns to dispense.

Look to see if you can define a table-returning function. This is relatively straightforward with PostgreSQL and other modern DBs. I can’t believe T-SQL doesn’t have something similar. Might be extra $$$ though.

T-SQL likes to call them Table Valued Functions, but really they’re just User defined functions with the RETURNS @table TABLE declaration.

You can get really fancy and use multi-statement table valued functions.

Would look something like this:

CREATE FUNCTION function_name()
   --columns in return table
    col1 AS type
    col2 AS type
   --query here
    INSERT INTO @myTable
    FROM some table

   --return data as table
    INSERT INTO @rtnTable
    SELECT * FROM @myTable
1 Like

I initially was told to do it as a function and I tried to do it as a function, the issue is that I had to do some logic that required exec sp_executesql. The issue is the table is an INSERT ONLY historical table and one of the parameters of the stored proecedure (or would be function) is asOfDate, so that another database user or perhaps on the HMI, a operator could see what the settings were as of a certain date. The business logic wanted it so that if a user put an as of date prior to when any were recorded, we returned the very first record, instead of nothing.

So the way I had to do it was in a loop for each TestingGrade (a column of the table from 1-8) and see -does a record exist for TestingGrade 1 as of this date? If so → grab most recent record prior to asOfDate, if not, grab first record. Then insert that into the results table and move onto the next TestingGrade to run the same logic.

I am sure some SQL Wizard could probably make this work in a creative query but it was beyond my ability and time constraints so I went with a loop and some dynamic sql to be run to do it, and dynamic sql (or at least exec sp_executesql) can only be done in stored procedures, hence the stored procedure.

I have not tried this myself but I was recently reading about the different uses of temporary tables. It looks like you may be able to store the results of the procedure into a temporary table and then query from the temporary table. You would have to test this since I am not sure if this is doable.

Here is a link to the article I was reading: Temporary Tables in SQL Server - Simple Talk

The innermost queries could use a UNION, like so, to avoid the if statement in your SQL:

Select Top 1 *
	Select Top 1 *
	From SomeTable
	Where t_stamp <= :asOfDate
	Order By t_stamp Desc
	Union All
	Select Top 1 *
	From SomeTable
	Where t_stamp > :asOfDate
	Order By t_stamp Asc
) innerq