SQL Server stored procedures tutorial: Write, tune and get examples

This SQL Server stored procedures tutorial covers three areas for simplifying database development. A stored procedure, sometimes called the work horse of the database, also provides an important layer of security between the user interface and database. But the process of grouping T-SQL statements to create stored procedures can be complex. In our tutorial, learn the basics for writing stored procedures followed by methods for tuning them, and, finally, browse our collection of stored procedure examples – for checking database and log file sizes and monitoring long-running jobs and much more.

This SQL Server stored procedures tutorial covers three areas for simplifying database development. If you use

certain queries over and over again, make life easier by putting the SQL statements into stored procedures – then you'll only need to write them once.

A stored procedure, sometimes called the work horse of the database, also provides an important layer of security between the user interface and database. But the process of grouping T-SQL statements to create stored procedures can be complex. In our tutorial, learn the basics for writing stored procedures followed by methods for tuning them, and, finally, browse our collection of stored procedure examples – for checking database and log file sizes and monitoring long-running jobs and much more.

 

 WHAT YOU'LL FIND IN THIS TUTORIAL:

   Writing SQL Server stored procedures
   Tuning SQL Server stored procedures
   Examples of SQL Server stored procedures

 
 

Writing SQL Server stored procedures

Write new stored procedures in .NET with SQL Server 2005's Common Language Runtime (CLR). In SQL Server 2000, there was really only one way to create a stored procedure: as a T-SQL statement. That was the course to take in every previous edition of SQL Server. But SQL Server 2005 makes it possible to write stored procedures (and functions, triggers and a number of other things) in the .NET family of languages -- mainly, VB.NET and C#. Take a look at these five common questions on methods of working with stored procedures in SQL Server 2005 and learn how to convert stored procedures to the CLR model in this tip.

  Stored procedures vs. functions

More on SQL stored procedures

Learn about SQL Server in-memory database features

Understand how SQL server and CLR stored procedures compare

Read about how SQL Server stored procedures are improving

In many instances you can accomplish the same task using either a SQL Server stored procedure or a function. Both can be custom defined and part of any application. To decide between using stored procedures vs. functions keep in mind the fundamental difference between them: a stored procedure is designed to return its output to the application. A User Defined Function (UDF) returns table variables, while a stored procedure can't return a table variable although it can create a table. Learn more differences in this expert advice.

  Stored procedures vs. dynamic SQL: When should you use each?

Stored procedures and dynamic SQL each have their place in the SQL Server world. In this tip, you'll see the best scenarios for using stored procedures vs. dynamic SQL. Stored procedures are useful when you have a function that changes relatively little or has parameters easily encompassed within the syntax of a stored procedure. Dynamic SQL on the other hand, is generally for quick-and-dirty work. It's also used when dealing with something that needs to be assembled almost entirely from scratch; For instance when the number and variety of parameters, the tables being queried, and so on may vary from statement to statement. But even then a stored procedure could be suitable and in the long run the better choice.

  Replicated stored procedure options with SQL Server 2005

Replicating stored procedures between SQL Server environments is a process for which you have many technologies to choose from. Know your options with SQL Server 2005, including native replication features, log shipping and manual and automated deployments. The technologies listed in this tip provide a means to replicate stored procedures between SQL Server environments.

  Configure SQL Server Service Broker for sending stored procedure data

Normally, when users fire a stored procedure in SQL Server, they must wait for the stored procedure to complete the data processing, before the next action can be completed. By using the Service Broker in SQL Server 2005 for sending stored procedure data, they can queue the actual processing of the data for a later date. Follow these steps for setting up Service Broker and experience a rock solid communications' platform for transmitting stored procedure data from one SQL Server system to another.

  Use table-valued parameters for SPs in SQL Server 2008

With table-valued parameters for stored procedures in SQL Server 2008, ordinary stored procedures have extraordinary abilities. Decrease I/O by passing variables as parameters in your stored procedure; You'll save time by making significantly fewer calls to SQL Server and fewer writes against the database. This tip takes a look at how to put table-valued parameters to work in your stored procedures, but first explains what the table data type is in SQL Server and how it works.

 
 

Tuning SQL Server stored procedures

  Tune stored procedures with exception handling in SQL Server 2005

Exception handling was widely thought to be one of the weakest aspects of T-SQL script writing. Fortunately, this has changed in SQL Server 2005, which supports structured error handling using TRY…CATCH. This tip focuses first on the basics of the new TRY…CATCH constructs in SQL Server 2005 and then looks at some sample SQL Server 2000 and 2005 T-SQL that produces constraint violations using transactional code.

    How to debug T-SQL stored procedures

Easy-to-use graphical debugging tools in Visual Studio 2005 will help simplify the process of unit testing your T-SQL code. This tip covers Visual Studio 2005 debugging capabilities. SQL Server expert, Joe Toscano executes a sample T-SQL stored procedure and assigns values to input parameters, inspects variable contents, follows the logical flow of the procedure during runtime, evaluates T-SQL expressions, views the procedure's output, sets breakpoints and generally examines the state of the environment.

   T-SQL performance problems and solutions

Poorly written Transact-SQL (T-SQL) code can cause serious performance issues; and fixing such problems by throwing more memory at the server is simply relieving pain rather than fixing the root cause. Using inline queries instead of stored procedures is typically a beginner's mistake. Some languages offer straightforward incorporation of queries sent from a front-end application to the database. You're guaranteed to notice suboptimal performance if the application is used by hundreds or even dozens of users. Find out what the deal is on using inline queries vs. stored procedures.

  Make changes to SQL Server stored procedures with batch editing

Many common database tasks are performed with SQL Server stored procedures. Sometimes you need to edit a group of stored procedures, which is the kind of surgery you want to perform as delicately as possible. This, of course, assumes you're not using Visual Studio or some other IDE to perform this kind of work, which takes a lot of the drudgery out of it. Here's a step-by-step process for making changes to a group of stored procedures at one time, whether you're using SQL Server 2000 or SQL Server 2005.

  Upgrading stored procedures in SQL Server 2005

Let's say you have a front-end application that's being written or managed by a different team and it isn't upgraded as often as the back-end database. You still want to upgrade the application's stored procedures to take advantage of new features in SQL Server 2005. You'll want to do this as gracefully as possible so the database and front-end application don't have a falling out. Learn how to upgrade your stored procedures to take advantage of new SQL Server 2005 features without breaking existing functionality after database migration.

 
 

Examples of SQL Server stored procedures

  Check SQL Server database and log file size

Knowing the size of a SQL Server database is one of the many DBA responsibilities that you can accomplish easily with the stored procedure sp_SDS. Not only will sp_SDS check SQL Database Space, but it can also be used to monitor database growth, alert a DBA on data or log file growth, execute a transaction log backup and even provide a detailed breakdown at the file level – so a DBA can then shrink files with the most empty space.

  Track changes to SQL Server 2000 and 2005 with one simple utility

As a DBA, you need to be aware of what's happening within your SQL Servers. This includes new objects, logins and even databases. But if you have dozens of SQL Servers – both 2000 and 2005 models – and permissions are shared, you'll have to go beyond the limits of triggers. SQL Server expert Michelle Gutzait shares some handy scripts in a utility she created – which involves stored procedures that monitor changes in system tables and send email alerts if any changes are detected.

  Find size of SQL Server tables and other objects

When determining SQL Server disk space used by an object, Microsoft's sp_spaceused has limits. Here's an original stored procedure, sp_SOS, that calculates specific object space in SQL Server. Use it to get an overview of user table sizes in a database, summary of the total space owned by a group of tables and to see the top 10 biggest indexed objects.

  Monitor long-running jobs in SQL Server 2000

Monitoring long-running jobs in SQL Server 2000 is not a simple practice, but it is a best practice among DBAs. In this tip, try out a stored procedure that identifies long-running jobs. You'll also receive email alerts about these jobs that poorly affect SQL Server performance.

  Find fragmented indexes in SQL Server

Fragmented indexes and tables in SQL Server can slow down application performance. Here's a stored procedure that finds fragmented indexes in all servers and databases.

  Determine last database backup

You have dozens or hundreds of SQL Servers to administer and you just want to know when the last backup was taken for each database on each instance. Run the stored procedure p_SelectLastDatabaseBackup and avoid any backup and recovery disasters due to a missing SQL Server backup.

  Who is running SQL Server backups and restores and when

Determining who issued SQL Server backup and restores and when they occurred is no simple task. But these two stored procedures find when backups and restores are being issued against your server. Run the stored procedures every day, to be sure backups are running properly. When you need to restore, use the history to see the order of the backups and which backup files exist and where they are physically located.

  A simple way to perform crosstab operations

Do you wish SQL Server had a crosstab feature like the one in Microsoft Access to aid in data analysis? Well, it does: the sp_Crosstab routine. A crosstab operation usually involves complicated T-SQL coding or pulling data into an application, but sp_Crosstab makes it simple.

  Execute T-SQL code from a file

Need to execute T-SQL code in a file from within other T-SQL code? Existing routines used to execute T-SQL code from a file, without using osql and xp_cmdshell, have many limitations. The sp_ExecuteSQLFromFile stored procedure addresses the shortcomings and adds new functionality.

  Generate code for ad hoc data operations

Performing ad hoc operations on data typically means having to manually enter T-SQL code in Query Analyzer. It can be difficult to perfect the syntax, and tedious to list column names once, twice or even three times. Fortunately, useful template code can be easily generated with a stored procedure, instead of being entered by hand. Save yourself some trouble with the sp_GenerateQuery routine.

   Search character columns for a given SQL Server string

The stored procedure Sp_FindString allows you to search for occurrences of a SQL Server string in the character columns (char, nchar, varchar, nvarchar) of a set of tables. The output includes the table name, column name and number of rows in which the SQL Server string was found.

  Perform specified operations on SQL tables and objects

To perform a particular operation on a specified set of tables or objects, here are two simple and flexible stored procedures: sp_FixTables and sp_FixObjects. These routines allow you to generate T-SQL code, immediately execute commands, more easily specify a subsets and perform character substitutions.

  Create a fixed-width file for exporting SQL Server data

Exporting SQL Server data to a file from within a stored procedure is a common task, but there are different ways to do this -- none of which are very easy. The sp_SaveFixedColumn stored procedure simplifies the export process by easily creating a file containing specific data.

  Simplify comma-separated value (CSV) exports in SQL Server

Comma-separated value (CSV) exports are often used to provide SQL Server data to Excel – but there is no simple way to export SQL Server data within a stored procedure to a file. The sp_SaveDelimitedColumns routine makes CSV exports in SQL Server a breeze by easily creating a file containing specific data.

  Keep track of SQL Agent jobs

Enterprise Manager's standard tools aren't much help in examining SQL Agent jobs. To get details about SQL jobs currently running, try this sp_ListJobInformation routine instead.

  Examining SQL Server Agent job run history

Using Enterprise Manager to examine SQL Server Agent job run history is a chore. The sp_ListJobRunHistory stored procedure provides handy filtering features to make it easy.

  Easily filter for SQL Server connections

In monitoring usage patterns on SQL Server connections, you need a tool to filter for connections of interest. Enterprise Manager has a basic tool to list connections, but it's not particularly flexible or convenient. The sp_ListConnections stored procedure makes filtering for SQL Server connections easy.

  List SQL Server database objects by selected type(s)

Get a list of all specified SQL Server database object with the stored procedure sp_ListObjects. This routine returns a list of all the current SQL Server database objects by type, including object types, object names, parent object names (where applicable) and creation dates (where available).

  Find SQL Server columns and tables

This routine is handy for finding SQL Server columns by name and listing the table(s) in which they exist. The listing includes table name, column name, ordinal position, data type, data size, width/precision and scale. Listing 1 creates a system stored procedure named sp_FindColumn. The routine returns a list of table/column names that match a specified combination of search criteria. Listing 2 creates a system stored procedure named sp_AnalyzeColumn. The routine returns a frequency distribution (counts and percentages) of the values in a specified table/column.

  Bonus examples of SQL Server stored procedures and parameters

How can SQL Server stored procedures be helpful if you don't know they exist? Database architect Denny Cherry shares 18 examples of handy stored procedures and their parameters that have gone undocumented by Microsoft.

This was first published in July 2008

Dig deeper on Microsoft SQL Server Performance Monitoring and Tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close