Microsoft SQL Server 2008 R2 Unleashed- P179:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1734 CHAPTER 44 Advanced Stored Procedure Programming and Optimization Check the error system function after each SQL statement especially insert update and delete to verify that the statements executed successfully. Return a status code other than 0 if a failure occurs. Be sure to comment your code so that when you or others have to maintain it the code is self-documenting. Consider using a source code management system such as Microsoft Visual Studio SourceSafe CVS or Subversion to maintain versions of your stored procedure source code. You should avoid using select in your stored procedure queries. If someone were to add columns to or remove columns from a table the stored procedure would generate a different result set which could cause errors with the applications. Whenever using INSERT statements in stored procedures you should always provide the column list associated with the values being inserted. This allows the procedure to continue to work if the table is ever rebuilt with a different column order or additional columns are added to the table. Listing demonstrates what happens if the column list is not provided and a column is added to the referenced table. LISTING Lack of Column List in INSERT Statement Causing Procedure to Fail use bigpubs2008 go IF EXISTS SELECT FROM WHERE schema_id schema_id dbo AND name N insert_publishers DROP PROCEDURE GO create proc insert_publishers pub_id char 4 pub_name varchar 40 city varchar 20 state char 2 country varchar 30 as INSERT INTO VALUES pub_id pub_name city state country if error 0 print New Publisher added go exec insert_publishers 9950 Sams Publishing Indianapolis IN USA go New Publisher added T-SQL Stored Procedure Coding Guidelines 1735 alter table publishers add street varchar 80 null go exec insert_publishers 9951 Pearson Education Indianapolis IN USA go Msg 213 Level 16 State 1 Procedure insert_publishers Line 7 Insert Error Column .
