A stored procedure is a set of SQL statements stored under a procedure name to be executed as a group by your database server. It can be executed quickly inside your database server, saving you the time it would take to do the same work on a client. In part one of our stored procedure series of fast guides, we highlighted some particularly useful undocumented stored procedures. Here we take a broader look at some of SQL Server's handy stored procedures.
Stored procedure basics
- Stored procedure primer
This chapter from The Guru's Guide to SQL Server Stored Procedures, XML and HTML, by Ken Henderson serves to prime the discussion on SQL Server stored procedure programming.
- Stored procedure expert technical advice
Get the stored procedure help you need in this topics section. You'll find expert advice on everything from using stored procedures to look at different databases to working with stored procedures in Crystal Reports.
- Stored procedure tips
Peruse our complete collection of tips on both stored procedures and undocumented stored procedures here.
- Restoring a database from another SQL Server
Restoring the database after migration is the easy part, but you may be left with mismatched logins and database users. The stored procedures in this tip will help you sort out your mismatches.
- Import data from other database using stored procedure
Learn how a stored procedure can be used to import data from other types of database's and text files to SQL Server.
- Stored procedure to reference data in another database
It is possible to have a stored procedure in one database look at tables in another. Development expert Andrew Novick tells you how in this expert response.
- Improve query performance without adding CompanyID to OrderStatusHistory table
Just a few small changes to a stored procedure could change your query performance. Performance and Tuning expert Jeremy Kadlec discusses ways to increase query performance by making alterations to your indexes instead of your stored procedures.
- Optimizing transactions through stored procedures
Stored procedures are a great way to optimize operations, so transactional processing in SQL Server is best done through a stored procedure. In this tip, contributor Serdar Yegulalp discusses optimizing transactional behavior in a stored procedure.
Query and operations
- Stored procedure: Search character columns for a given string
The sp_FindString routine allows you to search for occurrences of a 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 string was found.
- Stored procedure: Find columns and tables
This simple routine is handy for finding 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.
- Stored procedure: List database objects by selected type(s)
Brian Walker presents a simple stored procedure to return a list of all the objects of selected types in the current database.
- Stored procedures: Perform specified operations on tables and objects
To perform a particular operation on a specified set of tables or objects, contributor Brian Walker offers 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.
Click for the first part of our series: Fast Guide: Undocumented stored procedures