Get a specific column from a returned result of SP?

Question

I have a stored procedure that returns a result, let's say it return rows of products. But each product status is not in our hand(can't get it). Our DBA just gave us another stored procedure to get the status of a product. We need to get individual product status by calling their SP. Let's say we have Product table,

CREATE TABLE PRODUCTS
(
   ID INT,
   Name NVARCHAR(100)
)

CREATE PROCEDURE GetProducts
AS
BEGIN
      INSERT INTO #TEMPTABLE
       SELECT * FROM PRODUCTS; -- Yes Too Much simplified

      -- Create cursor and set additional status in #TEMPTABLE

END;

EXEC GetStatus @ProductId; -- SP That need to get status

The problem is that GetStatus is only way to get the status and this sp sometimes return 2 columns, sometimes 4 and sometimes 8. The return columns will always include Status column for sure.

If columns names is fixed then there is no problem. Is there is a way to create dynamic table at the time of executing SP.

Tried this but not working,

WITH DynamicTable AS
(
    EXEC GetStatus
)

See the top two answers here: http://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table — Nick, Aug 05 '16 at 18:06
The usage of cursors and #temptable within a stored proc is likely another sign your DBA isn't up to the tasks — Twelfth, Aug 05 '16 at 18:07
Did you look at the second answer in the question? I mentioned the "top two" because I was not sure the accepted answer would work for your configuration. — Nick, Aug 05 '16 at 18:08
@RossPresser Twelfth this is very old SP and include lot of logic. — Imran Qadir Baksh - Baloch, Aug 05 '16 at 18:08
Just add more columns than you would expect and make sure that they are nullable.... — Nick, Aug 05 '16 at 18:09
Yup getting error variable number of columns. I have 11 columns and if sp returns 7 columns I am getting errors — Imran Qadir Baksh - Baloch, Aug 05 '16 at 18:11
Why are the columns variable? Is it variable each time you execute it? — Nick, Aug 05 '16 at 18:16
@Nicarus the sp that DBA give us return dynamic number of columns. — Imran Qadir Baksh - Baloch, Aug 05 '16 at 18:18
Yes, I know. Do you know why it does that? In my experience, that is very uncommon. — Nick, Aug 05 '16 at 18:19
@Nicarus Yup its very old and big SP and include lot of our bossiness(yes poorly implemented) and changed by lot of peoples. That's the problem. But one thing sure its returns Status. — Imran Qadir Baksh - Baloch, Aug 05 '16 at 18:21
It's old and big is not an explanation for why it returns variable columns. — Nick, Aug 05 '16 at 18:22
Do you have permission to create stored procedures and user defined functions? — Tab Alleman, Aug 05 '16 at 18:25
As has been said, you are severly handicapped by having to do things this way and you need a query that returns a list of products and their status with a fixed number of other fields. The only thing I have to add is if you really cant rewrite the thing, run it overnight for all products using the hideous cursor etc and produce a table your system can query. Obviously the data will be a day stale so whether that will work depends on the business requirement etc — Jonathan Shields, Aug 05 '16 at 20:35

score 3 · Accepted Answer · answered Aug 05 '16 at 18:34

3

The answer to your question is no. There is no good way to get the value of a specific column returned by a stored procedure that can return a dynamic set of columns.

I say no "good" way, because of course there's a WAY. You can write an INSERT EXEC statement for every possible set of columns that the procedure can return and wrap each one in a TRY..CATCH block. If the first one errors, try the next one. As soon as you hit one that doesn't error, get the Status from it, and skip the rest.

That's the answer to your question. The solution to your problem, however, is to replace the GetStatus stored procedure with a Table-valued function. Then you can select from it, join to it, etc. I think the function would have to always return a consistent number of columns, but that would be better anyway, and the columns that aren't needed in a specific case could just be left empty or NULL.

answered Aug 05 '16 at 18:34

Tab Alleman

31,483
7
36
52

I like the re-write to return the same number of columns a lot. If there is one shared column then we expect 12 columns in the new table (1+(2-1)+(4-1)+(8-1)) – Hogan Aug 05 '16 at 18:36
@Hogan (and Tab) Did SQL have a table type with dynamic columns. Like in dynamic languages Python, Js, C#(dynamic keyword), Ruby ? – Imran Qadir Baksh - Baloch Aug 05 '16 at 18:46
1

There's no way in SQL Server to declare a table type with dynamic columns, but you can declare it, and then ALTER it if circumstances require it. However, I don't know if you can ALTER the Table that is going to be output by a TVF, which why I say I *think* the function would have to have a consistent set of columns. – Tab Alleman Aug 05 '16 at 18:53
2

@user960567 - SQL was create over 50 years before any of those languages existed. There is no way to have "dynamic tables" in SQL. End of story. – Hogan Aug 05 '16 at 18:55

Get a specific column from a returned result of SP?

1 Answers1