http://www.sqlservercentral.com/Content tagged Advanced Querying, Stored Procedures posted on SQLServerCentral.comen-us360sjones@sqlservercentral.com (Steve Jones)Optional parameters are easily handled with NULL values in T-SQL, but there are cases where this doesn't always work. Alex Grinberg brings us a new technique to allow your stored procedures to be structured to deal with this situation without dynamic SQL. http://www.sqlservercentral.com/articles/Advanced+Querying/2638/2006/10/09http://www.sqlservercentral.com/articles/Advanced+Querying/2638/Robert is our expert on dynamic sql. This week he offers some good hints for planning the contruction of a proc that will use dynamic sql. He also adds some suggestions on how to format the code so that when you return to it later, you can figure out what you were doing! http://www.sqlservercentral.com/articles/Miscellaneous/howtobuilddynamicstoredprocedures/968/2005/12/16http://www.sqlservercentral.com/articles/Miscellaneous/howtobuilddynamicstoredprocedures/968/Error handling in SQL Server 2000 is tricky. And it isn't the most robust system in the world. Stefan Popovski started a series on error handling using @@error and xact_abort. Here's the second installment of this series looking at error handling in nested stored procedures. http://www.sqlservercentral.com/articles/Advanced+Querying/bepreparedpart2/1386/2004/06/08http://www.sqlservercentral.com/articles/Advanced+Querying/bepreparedpart2/1386/Robert is our expert on dynamic sql. This week he offers some good hints for planning the contruction of a proc that will use dynamic sql. He also adds some suggestions on how to format the code so that when you return to it later, you can figure out what you were doing! http://www.sqlservercentral.com/articles/Miscellaneous/howtobuilddynamicstoredprocedures/968/2005/12/16http://www.sqlservercentral.com/articles/Miscellaneous/howtobuilddynamicstoredprocedures/968/Are you using default values for your parameters? Using named parameters when you call the proc or passing the values by ordinal? Should you be? Andy thinks 6 out of 10 of our readers will agree with his point of view, we'll be a little more conservative and guess that 5 of out 10 will be closer. http://www.sqlservercentral.com/articles/Miscellaneous/defaultvaluesandnamedparametersforstoredprocs/882/2003/01/08http://www.sqlservercentral.com/articles/Miscellaneous/defaultvaluesandnamedparametersforstoredprocs/882/In this article, we will leverage Extended Stored Procedures to extend the functionality of SQL Server and give a starting point to experiment with Extended Stored Procedures. http://www.sqlservercentral.com/articles/Advanced+Querying/doevents/784/2002/08/26http://www.sqlservercentral.com/articles/Advanced+Querying/doevents/784/This article shows you one of the most time saving stored procedure built into SQL Server and then shows you how you can modify it to affect additional objects like triggers. For example, after this article and the code in the article, you'll be able to disable every trigger in your system in one line of code. http://www.sqlservercentral.com/articles/Administering/sp_msforeachworker/711/2002/06/17http://www.sqlservercentral.com/articles/Administering/sp_msforeachworker/711/Building and executing dynamic sql in a stored procedure - is it the only way to solve problems like supporting a simple search function? Leon offers a couple alternatives that let you continue to provide the functionality in a stored procedure without using dynamic sql. Interesting ideas worth exploring! http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/makingdynamicqueriesstatic/672/2002/05/02http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/makingdynamicqueriesstatic/672/Sp_MSforeachdb gives a DBA the ability to cycle through every database in your catalog. Find out how to use it. http://www.sqlservercentral.com/articles/Advanced+Querying/sp_msforeachdb/182/2001/05/31http://www.sqlservercentral.com/articles/Advanced+Querying/sp_msforeachdb/182/