Tsql Prosedüre select atmak

INSERT-EXEC

Overview

INSERT-EXEC is a method that has been in the product for a long time. It's a method that is seemingly very appealing, because it's very simple to use and understand. Also, it permits you use the result of a stored procedure without any changes to it. Above we had the example with the procedure SalesByStore. Here is a how we can implement BigSalesByStore with INSERT-EXEC:

CREATE PROCEDURE SalesByStore @storeid varchar(30) AS
   SELECT t.title, s.qty
   FROM   sales s
   JOIN   titles t ON t.title_id = s.title_id
   WHERE  s.stor_id = @storeid
go
CREATE PROCEDURE BigSalesByStore @storeid varchar(30),
                                 @qty     smallint AS
   
   CREATE TABLE #SalesByStore(title varchar(80) NOT NULL PRIMARY KEY,
                              qty   smallint    NOT NULL)
   
   INSERT #SalesByStore (title, qty)
      EXEC SalesByStore @storeid

   SELECT * FROM #SalesByStore WHERE qty >= @qty
go
EXEC SalesByStore '7131'
EXEC BigSalesByStore '7131', 25
go
DROP PROCEDURE SalesByStore, BigSalesByStore

In this example, I receive the data in a temp table, but it could also be a permanent table or a table variable. (Except on SQL 2000, where you cannot use a table variable.)
It cannot be denied that this solution is simpler than the solution with sharing a temp table. So why then did I first present a more complex solution? Because when we peel off the surface, we find that this method has a couple of issues that are quite problematic.

It Can't Nest

If you for some reason would try:

CREATE TABLE #BigSalesByStore(titleid varchar(80) NOT NULL PRIMARY KEY,
                              qty     smallint    NOT NULL)
INSERT #BigSalesByStore (titleid, qty)
   EXEC BigSalesByStore '7131', 25

SQL Server will tell you:

Msg 8164, Level 16, State 1, Procedure BigSalesByStore, Line 8
An INSERT EXEC statement cannot be nested.

This is a restriction in SQL Server and there is not much you can do about it. Except than to save the use of INSERT-EXEC until when you really need it. That is, when rewriting the callee is out of the question, for instance because it is a system stored procedure.

There is a Serious Maintenance Problem

Six months later there is a user requirement for the application function that uses the result set from SalesByStore that the column title_id should be displayed. A developer merrily adds the column to the result set. Unfortunately, any attempt to use the function calling BigSalesByStore now ends in tears:

Msg 213, Level 16, State 7, Procedure SalesByStore, Line 2
Column name or number of supplied values does not match table definition.

What it says. The result set from the called procedure must match the column list in the INSERT statement exactly. The procedure may produce multiple result sets, and that's alright as long as all of them match the INSERT statement.
From my perspective, having spent a lot of my professional life with systems development, this is completely unacceptable. Yes, there are many ways to break code in SQL Server. For instance, a developer could add a new mandatory parameter to SalesByStore and that would also break BigSalesByStore. But most developers are aware the risks with such a change to an API and therefore adds a default value for the new parameter. Likewise, most developers understand that removing a column from a result set could break client code that expects that column and they would not do this without checking all code that uses the procedure. But adding a column to a result set seems so innocent. And what is really bad: there is no way to find out that there is a dependency – save searching through all the database code for calls.
Provided that you may alter the procedure you are calling, there are two ways to alleviate the problem. One is simply to add a comment in the code of the callee, so that the next developer that comes around is made aware of the dependency and hopefully changes your procedure as well.
Another way is to use table types (if you are on SQL 2008 or later). Here is an example:

CREATE TYPE SalesByStore_tbl AS TABLE 
     (titleid varchar(80) NOT NULL PRIMARY KEY,
      qty     smallint    NOT NULL)
go
CREATE PROCEDURE SalesByStore @storeid varchar(30) AS
   DECLARE @ret SalesByStore_tbl
   INSERT @ret (titleid, qty)
      SELECT t.title, s.qty
      FROM   sales s
      JOIN   titles t ON t.title_id = s.title_id
      WHERE  s.stor_id = @storeid
   SELECT * FROM @ret
go
CREATE PROCEDURE BigSalesByStore @storeid varchar(30),
                                 @qty     smallint AS
   DECLARE @data SalesByStore_tbl
   INSERT @data
      EXEC SalesByStore @storeid
   SELECT title, qty FROM @data WHERE qty >= @qty
go
EXEC SalesByStore '7131'
EXEC BigSalesByStore '7131', 25
go
DROP PROCEDURE SalesByStore, BigSalesByStore
DROP TYPE SalesByStore_tbl

It is interesting to note that this code makes virtue of two things that usually are bad practice, to wit SELECT * and INSERT with out an explicit column list. This is not a matter of sloppiness – it is essential here. If someone wants to extend the result set of SalesByStore, the developer has to change the table type, and BigSalesByStore will survive, even if the developer does not know about its existence.
You could argue that this almost like an output TVP, but don't forget the other problems with INSERT-EXEC – of which there are two more to cover.

The Procedure is Executed in the Context of a Transaction

Even if there is no explicit transaction started with BEGIN TRANSACTION, an INSERT statement constitutes a transaction of its own. (So that the statement can be rolled back in case of an error.) That includes any procedure called through INSERT-EXEC. Is this bad or not? In many cases, this is not much of an issue. But there are a couple of situations where this can cause problems:

• The procedure performs an update intended to be quick. Locks are now held for a longer duration, which may cause contention problems.
• The isolation level is REPEATABLE READ or SERIALIZABLE, as opposed to the default READ COMMITTED. This also causes locks to held longer than intended.
• Some system procedures disagree to be called within a transaction.
• If the procedure accesses a linked server, you now have a distributed transaction. Distributed transactions are sometimes difficult to get working. See more about this in the closing chapter on linked servers.

Rollback and Error Handling is Difficult

In my article on error handling, I suggest that you should always have an error handler like

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC error_handler_sp
   RETURN 55555
END CATCH

The idea is that even if you do not start a transaction in the procedure, you should always include a ROLLBACK, because if you were not able to fulfil your contract, the transaction is not valid.
Unfortunately, this does not work well with INSERT-EXEC. If the called procedure executes a ROLLBACK statement, this happens:

Msg 3915, Level 16, State 0, Procedure SalesByStore, Line 9
Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

The execution of the stored procedure is aborted. If there is no CATCH handler anywhere, the entire batch is aborted, and the transaction is rolled back. If the INSERT-EXEC is inside TRY-CATCH, that CATCH handler will fire, but the transaction is doomed, that is, you must roll it back. The net effect is that the rollback is achieved as requested, but the original error message that triggered the rollback is lost. That may seem like a small thing, but it makes troubleshooting much more difficult, because when you see this error, all you know is that something went wrong, but you don't know what.
And, no, before you ask, there is no way to find out at run-time that you are called from INSERT-EXEC.

Dynamic SQL

You can also use INSERT-EXEC with dynamic SQL:

INSERT #tmp (...)
   EXEC sp_executesql @sql, @params, @par1, ...

Presumably, you have created the statement in @sql within your stored procedure, so it is unlikely that a change in the result set will go unnoticed. So from this perspective, INSERT-EXEC is fine. But the restriction that INSERT-EXEC can't nest remains, so if you use it, no one can call you with INSERT-EXEC. For this reason, in many cases it is better to put the INSERT statement inside the dynamic SQL.
There is also a performance aspect, that SQL Server MVP Adam Machanic has detailed in a blog post. The short summary is that with INSERT-EXEC, data does not go directly to the target table but bounces over a "parameter table", which incurs some overhead. Then again, if your target table is a temp table, and you put the INSERT inside the dynamic SQL, you may face a performance issue because of recompilation.
Occasionally, I see people who use INSERT-EXEC to get back scalar values from their dynamic SQL statement, which they typically invoke with EXEC(). In this case, you should not use INSERT-EXEC at all, but instead use sp_executesql which permits you to use OUTPUTparameters. Dynamic SQL is a complex topic, and if you are not acquainted with it, I recommend you to read my article The Curse and Blessings of Dynamic SQL.

Conclusion

INSERT-EXEC is simple to use, and if all you want to do is to grab a big result set from a stored procedure for further analysis ad hoc, it's alright.
But you should be very restrictive to use it in application code. Only use it when rewriting the procedure you are calling is completely out of the question. That is, the procedure is not part of your application: a system stored procedure or part of a third-party product. And in this case, you should make it a routine to always test your code before you take a new version of the other product in use.


Kaynak : http://www.sommarskog.se/share_data.html#INSERTEXEC