Sql – grabbing first result set from a stored proc called from another stored proc

sql serversql-server-2005stored-procedures

I have a SQL Server 2005 stored proc which returns two result sets which are different in schema.

Another stored proc executes it as an Insert-Exec. However I need to insert the first result set, not the last one. What's a way to do this?

I can create a new stored proc which is a copy of the first one which returns just the result set I want but I wanted to know if I can use the existing one which returns two.

Best Answer

Actually, INSERT..EXEC will try to insert BOTH datasets into the table. If the column counts match and the datatype can be implicitly converted, then you will actually get both.

Otherwise, it will always fail because there is no way to only get one of the resultsets.

The solution to this problem is to extract the functionality that you want from the called procedure and incorporate it into the (formerly) calling procedure. And remind yourself while doing it that "SQL is not like client code: redundant code is more acceptable than redundant data".

In case this was not clear above, let me delineate the facts and options available to anyone in this situation:

1) If the two result sets returned are compatible, then you can get both in the same table with the INSERT and try to remove the ones that you do not want.

2) If the two result sets are incompatible then INSERT..EXEC cannot be made to work.

3) You can copy the code out of the called procedure and re-use it in the caller, and deal with the cost of dual-editing maintenance.

4) You can change the called procedure to work more compatibly with your other procedures.

Thats it. Those are your choices in T-SQL for this situation. There are some additional tricks that you can play with SQLCLR or client code but they will involve going about this a little bit differently.

Related Solutions

Sql-server – What’s the difference between a temp table and table variable in SQL Server

There are a few differences between Temporary Tables (#tmp) and Table Variables (@tmp), although using tempdb isn't one of them, as spelt out in the MSDN link below.

As a rule of thumb, for small to medium volumes of data and simple usage scenarios you should use table variables. (This is an overly broad guideline with of course lots of exceptions - see below and following articles.)

Some points to consider when choosing between them:

Temporary Tables are real tables so you can do things like CREATE INDEXes, etc. If you have large amounts of data for which accessing by index will be faster then temporary tables are a good option.
Table variables can have indexes by using PRIMARY KEY or UNIQUE constraints. (If you want a non-unique index just include the primary key column as the last column in the unique constraint. If you don't have a unique column, you can use an identity column.) SQL 2014 has non-unique indexes too.
Table variables don't participate in transactions and SELECTs are implicitly with NOLOCK. The transaction behaviour can be very helpful, for instance if you want to ROLLBACK midway through a procedure then table variables populated during that transaction will still be populated!
Temp tables might result in stored procedures being recompiled, perhaps often. Table variables will not.
You can create a temp table using SELECT INTO, which can be quicker to write (good for ad-hoc querying) and may allow you to deal with changing datatypes over time, since you don't need to define your temp table structure upfront.
You can pass table variables back from functions, enabling you to encapsulate and reuse logic much easier (eg make a function to split a string into a table of values on some arbitrary delimiter).
Using Table Variables within user-defined functions enables those functions to be used more widely (see CREATE FUNCTION documentation for details). If you're writing a function you should use table variables over temp tables unless there's a compelling need otherwise.
Both table variables and temp tables are stored in tempdb. But table variables (since 2005) default to the collation of the current database versus temp tables which take the default collation of tempdb (ref). This means you should be aware of collation issues if using temp tables and your db collation is different to tempdb's, causing problems if you want to compare data in the temp table with data in your database.
Global Temp Tables (##tmp) are another type of temp table available to all sessions and users.

Some further reading:

Martin Smith's great answer on dba.stackexchange.com
MSDN FAQ on difference between the two: https://support.microsoft.com/en-gb/kb/305977
MDSN blog article: https://docs.microsoft.com/archive/blogs/sqlserverstorageengine/tempdb-table-variable-vs-local-temporary-table
Article: https://searchsqlserver.techtarget.com/tip/Temporary-tables-in-SQL-Server-vs-table-variables
Unexpected behaviors and performance implications of temp tables and temp variables: Paul White on SQLblog.com

Sql-server – Check if table exists in SQL Server

For queries like this it is always best to use an INFORMATION_SCHEMA view. These views are (mostly) standard across many different databases and rarely change from version to version.

To check if a table exists use:

IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'TheSchema' 
                 AND  TABLE_NAME = 'TheTable'))
BEGIN
    --Do Stuff
END

Best Answer

Related Solutions

Sql-server – What’s the difference between a temp table and table variable in SQL Server

Sql-server – Check if table exists in SQL Server

Related Topic