Fast Guide

Fast Guide: Stored procedures

A stored procedure is a set of SQL statements stored under a procedure name to be executed as a group by your

    Requires Free Membership to View

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.

TABLE OF CONTENTS
   Basics
   Data migration
   Performance
   Query and operations

  Stored procedure basics Return to Table of Contents

  • 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.

      Data migration Return to Table of Contents

  • 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.

      Performance Return to Table of Contents

  • 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 Return to Table of Contents

  • 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


    This was first published in September 2005

  • There are Comments. Add yours.

     
    TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

    REGISTER or login:

    Forgot Password?
    By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
    Sort by: OldestNewest

    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: