Categories
Data

Modify column list from a called stored procedure

I needed to trim down the number of columns returned from a system procedure (eg. for performance) and unfortunately [more] it's not possible to simply call:

SELECT * FROM EXEC <stored proc to call> 

Eventually, I had to resort to an INSERT-EXEC approach (insert the result set resulting from an EXEC <sp> call to a temp table).

This is susceptible to any signature changes in the called procedure but since it's a system stored procedure it's likely to get changed (hopefully even in next/upcoming versions). If it would have been a custom stored procedure, there would have been better ways to do it but since it's a system SP, had to resort to this). Other options include OPENQUERY or CURSOR but the INSERT-EXEC seems to be the lesser evil.

For more info, check out this link on How to Share Data between Stored Procedures