http://www.sqlservercentral.com/Articles tagged T-SQL, Stored Procedures posted on SQLServerCentral.comen-us360sjones@sqlservercentral.com (Steve Jones)In the first article of a new series on T-SQL tips, Jacob Sebastian brings us a very useful technique. How to pass a table to a stored procedure so some set of rows can be operated on using some business logic. http://www.sqlservercentral.com/articles/Stored+Procedures/2977/2008/05/02http://www.sqlservercentral.com/articles/Stored+Procedures/2977/QL 2005 adds two new methods – signing with certificates and impersonation with EXECUTE AS – that can manage cases where the classic method of ownership chaining fails. I explain the two new methods, as well as the old one, and warns you about the pitfalls.http://www.sqlservercentral.com/redirect/articles/62314/2008/04/15http://www.sqlservercentral.com/redirect/articles/62314/Different ways of passing sets of data between stored procedures.http://www.sqlservercentral.com/redirect/articles/62313/2008/04/08http://www.sqlservercentral.com/redirect/articles/62313/Stored procedures can be an effective way to handle conflicting needs, but it's not always so obvious how to write them so they both perform well and scale.http://www.sqlservercentral.com/redirect/articles/2838/2008/01/30http://www.sqlservercentral.com/redirect/articles/2838/In the first article of a new series on T-SQL tips, Jacob Sebastian brings us a very useful technique. How to pass a table to a stored procedure so some set of rows can be operated on using some business logic. http://www.sqlservercentral.com/articles/Stored+Procedures/2977/2008/05/02http://www.sqlservercentral.com/articles/Stored+Procedures/2977/Regular columnist Robert Marda writes about the basics of using output parameters. If you're not using output params we hope this article will get you started - they are a great way to return less data to the client, perfect if you need only a few values and not a recordset/resultset. http://www.sqlservercentral.com/articles/Stored+Procedures/outputparameters/1200/2007/10/26http://www.sqlservercentral.com/articles/Stored+Procedures/outputparameters/1200/In the first article of a new series on T-SQL tips, Jacob Sebastian brings us a very useful technique. How to pass a table to a stored procedure so some set of rows can be operated on using some business logic. http://www.sqlservercentral.com/articles/Stored+Procedures/2977/2008/05/02http://www.sqlservercentral.com/articles/Stored+Procedures/2977/We're pleased to announce that Robert will be writing for us each month - generally on stored procedures, but occasionally on a different topic. This article discusses how nesting stored procedures works and how to use @@NextLevel. Good reading! If there is a stored procedure topic you'd like to see covered, add a comment to the article or email us at articles@sqlservercentral.com http://www.sqlservercentral.com/articles/Basic+Querying/nestingstoredprocedures/998/2007/05/18http://www.sqlservercentral.com/articles/Basic+Querying/nestingstoredprocedures/998/In the fourth installment of this series, Jacob Sebastian moves on to SQL Server 2005 and the new XML capabilities that make working with XML data easier than ever. http://www.sqlservercentral.com/articles/Stored+Procedures/2912/2007/04/05http://www.sqlservercentral.com/articles/Stored+Procedures/2912/In the previous articles, Jacob Sebastian looked at using XML to save a sales order with variable numbers of line items to a SQL Server 2000 database. In this part, he expands upon the processing to access that data from multiple nodes. http://www.sqlservercentral.com/articles/Miscellaneous/2911/2007/04/03http://www.sqlservercentral.com/articles/Miscellaneous/2911/In the first part of this series, Jacob Sebastion showed us how to use XML to save a sales order to the database with a variable amount of line items. In part 2, he continues looking at XML in SQL Server 2000 with some advanced XML processing. http://www.sqlservercentral.com/articles/Miscellaneous/2909/2007/03/29http://www.sqlservercentral.com/articles/Miscellaneous/2909/How many times have you tried to save a sales order to your database? For many DBAs this is a common scenario and one of the challenges is the many round trips for the various line items. Jacob Sebastian brings us the first part of a four part series looking at how you can use XML to reduce the round trips in SQL Server 2000. http://www.sqlservercentral.com/articles/Miscellaneous/2908/2007/03/27http://www.sqlservercentral.com/articles/Miscellaneous/2908/Grant Fritchey steps into the workbench arena, with an example-fuelled examination of catching and gracefully handling errors in SQL 2000 and 2005, including worked examples of the new TRY..CATCH capabilities.http://www.sqlservercentral.com/redirect/articles/2883/2007/03/06http://www.sqlservercentral.com/redirect/articles/2883/The third article in a four part series, this week Andy shows how to use the command object to work with stored procedure parameters. http://www.sqlservercentral.com/articles/Stored+Procedures/introductiontoadothecommandobject/535/2007/02/09http://www.sqlservercentral.com/articles/Stored+Procedures/introductiontoadothecommandobject/535/Stored procedures can be an effective way to handle conflicting needs, but it's not always so obvious how to write them so they both perform well and scale.http://www.sqlservercentral.com/redirect/articles/2838/2008/01/30http://www.sqlservercentral.com/redirect/articles/2838/SQL Server does a great job of handling concurrency & ensuring that users can make changes in multi-user systems without conflict. However there are times a strict calling order is needed. http://www.sqlservercentral.com/articles/Miscellaneous/2649/2006/10/17http://www.sqlservercentral.com/articles/Miscellaneous/2649/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/While the strength of SQL Server is not in string manipulation, it can be very handy when backing a web site to be able to automatically include links for some of your content. New author Grey Wilson brings us an easy technique to easily deliver results to developers with URLs embedded inside. http://www.sqlservercentral.com/articles/Stored+Procedures/addinginhtmllinks/2518/2006/08/08http://www.sqlservercentral.com/articles/Stored+Procedures/addinginhtmllinks/2518/Attaching and detaching databases is old hat these days right? Do you know how to reattach a database that has more than 16 files? Or do you know what happens if you try to reattach a database that had two log files but one is missing/deleted? And even if you know the answer to that - do you know how to fix it without restoring from backup? Maybe it's not ALL old hat just yet! http://www.sqlservercentral.com/articles/Administering/attachanddetachagain/656/2006/06/23http://www.sqlservercentral.com/articles/Administering/attachanddetachagain/656/Part 1 of a 4 part series about ADO, this is a beginner level article designed to get you started using the ADO connection object. If you haven't used ADO so far, why not see what it's all about? http://www.sqlservercentral.com/articles/Miscellaneous/introductiontotheadoconnectionobject/510/2006/05/26http://www.sqlservercentral.com/articles/Miscellaneous/introductiontotheadoconnectionobject/510/Performance tuning is an ongoing battle in SQL Server, but having a little knowledge up front when designing an application can greatly reduce the efforts. Do you know which performs better: stored procedures or triggers? There aren't many places where the two are interchangeable, but knowing the impacts of each might change the way you build an application. Read about this analysis by Vijaya Kumar. http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/storedprocedurevstriggers/1449/2006/03/31http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/storedprocedurevstriggers/1449/T-SQL has some well known limitations when working with parameters for a stored procedure, not the least of which is a variable number of parameters. While there are some solutions, they can be cumbersome to work with. Sloan Holliday brings us a creative solution using XML that can solve many issues. http://www.sqlservercentral.com/articles/Stored+Procedures/thezerotonparameterproblem/2283/2006/02/28http://www.sqlservercentral.com/articles/Stored+Procedures/thezerotonparameterproblem/2283/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/Do you know the difference between a login and a user? What's the best way to add them; Enterprise Manager, T-SQL, or SQL-DMO? In this beginner level article Andy demonstrates how to use all three methods to add logins and users and offers his view of which is the best technique. http://www.sqlservercentral.com/articles/Administering/loginsusersandrolesgettingstarted/514/2005/09/30http://www.sqlservercentral.com/articles/Administering/loginsusersandrolesgettingstarted/514/We've had a lot of coverage of dynamic sql (including another great one from Robert Marda later this week) but this one is a little different. Done in a question/answer format, Andy tries to explain to junior developers why dynamic sql is to be avoided, how to do so, what to do when you can't. http://www.sqlservercentral.com/articles/Performance+Tuning/dynamicsqlorstoredprocedure/969/2005/08/26http://www.sqlservercentral.com/articles/Performance+Tuning/dynamicsqlorstoredprocedure/969/Mindy explores the metadata stored in SQL 2000 to show you how to produce a simple and useful data dictionary! http://www.sqlservercentral.com/articles/Installation/datadictionaryfromwithinsqlserver2000/607/2005/02/04http://www.sqlservercentral.com/articles/Installation/datadictionaryfromwithinsqlserver2000/607/One of the biggest performance gains built into SQL Server is the stored procedure. In this article by Brian Kelley, he shows you how to fully utilize, debug and monitor the caching of such objects. http://www.sqlservercentral.com/articles/Administering/procedurecache/591/2004/11/19http://www.sqlservercentral.com/articles/Administering/procedurecache/591/SQL Server has some great solutions for writing reports easily, ensuring quick service for your customers. However many of the quick ways of generating reports from the database include the column headers in the results. And often clients who expect customized work want to see labels that are more familiar to them. Leo Peysakhovich has developed a way that allows him to easily return custom labels from his stored procedures. http://www.sqlservercentral.com/articles/Stored+Procedures/customizedoutputlabels/1532/2004/10/14http://www.sqlservercentral.com/articles/Stored+Procedures/customizedoutputlabels/1532/Performance tuning is an ongoing battle in SQL Server, but having a little knowledge up front when designing an application can greatly reduce the efforts. Do you know which performs better: stored procedures or triggers? There aren't many places where the two are interchangeable, but knowing the impacts of each might change the way you build an application. Read about this analysis by Vijaya Kumar. http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/storedprocedurevstriggers/1449/2006/03/31http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/storedprocedurevstriggers/1449/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/