I came across a requirement to insert result of stored procedure into a global temp table. Stored procedure name will be passed as a paramter.
1) I can’t use INSERT INTO TableName EXEC StoredProcName as diff stored procedure can return different schemas based on parameter.
This is a classic sticking point. The best methods for getting stuff done within SQL Server and T-SQL require that you know, control, and define well known and well defined structures. You're saying that everything is ad hoc, but, you want to use the most efficient mechanisms. You can't have it both ways. If you have an ad hoc system, you'll have to code around the ad hoc nature of that system, which means you won't get the efficiencies that are designed into the structured aspects of the system. If you want those, you need to modify your approach so that it's structured.
answered Jul 29 at 11:28 AM
Grant Fritchey ♦♦