select ... from sProc

[leonjollans]

This feels like it should be obvious, but it's escaping me just now.
I have a particularly complex stored procedure that runs dynamic sql to return a dynamic result set. I now want to select a subset of rows and columns from a known set of results.

in theory, I need to do something like

[blue]select[/blue] obj_id, obj_name, dynamic_field_1 [blue]from[/blue] ( [blue]exec[/blue] my_storedProcedure 0,2 )

I'm sure I've done something like this before but I just can't remember how to do it.

Can U Help )

 

[iker3000]

Hi!

You can't do that. Instead of that you can create a temp table, insert the result in this one (INSERT INTO #table_name EXEC your_stored_procedure) and than work with this (I'm not sure but I think, that you can't use a table varieble in this place instead of the temp table). However you have to be carefull with the INSERT INTO ... EXEC ... statement, since it can not be nested.

I hope this will help

IKER

 

[leonjollans]

hm. thanks iker3000.
that's what I did in the end. I don't like using temp tables if I can avoid it, especially not here, as I expect this procedure to get a lot of activity.

I tried inserting into a table variable, but it wouldn't allow me from an execute, The only way of keeping this in memory I can see is to run a cursor over the result set, storing *every column* in a variable, and inserting the ones i want in a table variable. But it doesn't look like I can do that either

temp table it is then, unless anyone knows a better way

thanks again

 

[TimKThailand]

You can also use Function to help.
Check 'User-Defined Functions That Return a table Data Type' on SQL Server Books Online out.