Mark White's BI Blog

  Here's how to use table typed arguments to author a stored procedure that inserts multiple master-detail records into tables that use identity columns for primary key. Consider the following Accounts and Transactions tables: CREATE TABLE Accounts (     AccountID int not null identity(1,1) PRIMARY KEY,     CustomerName NVARCHAR(100) not null,     AccountType NVARCHAR(10) not null     )          CREATE TABLE [Transactions] (     AccountID int not null,     TransactionID int not null identity(1,1),     TransactionDate date default (GETDATE()),     Amount money not null default (0.00),     CONSTRAINT PK_Transactions PRIMARY KEY (AccountID, TransactionID),     CONSTRAINT FK_TransactionAccount FOREIGN KEY (AccountID) REFERENCES Accounts(AccountID) ) ...

It's a discussion that comes up time after time in training session... when and why to use stored procedures. There are many heated debates posted on the net about this, with people seeming to argue strongly either for or against. Rather than suggest that you should or shouldn't use stored procedures with SQL server, let's look at their merits and pit falls with a view to knowing when to use them. The biggest misconception about stored procedures is that they offer significant performance advantages over submitted DML queries to the SQL engine. While it is true that the query plan for any stored procedures is precompiled, the SQL engine does a very good job of parameterizing DML queries and caching their plans. The real-world result is that stored procedures don't offer a significant improvement in performance – especially not in a world where disk I/O, no CPU, is the bottle neck. So then – if performance isn't a core criterion for choosing whether or not to use ...