Temp table in stored proc (SQL Server)

[Ursego]

Link to this posting

by Ursego » 22 Feb 2013, 08:28

If a stored procedure uses a temporary table to form its returned recordset, the following rules would be helpful:

1. "CREATE TABLE #recordset...", "INSERT #recordset..." and the final accord of your symphony - the "SELECT ... FROM #recordset" statements should have the fields listed in the same order. Don't forget this rule when you are adding a field to the proc's recordset! One of the main concepts of relational databases is "access fields by their names, not locations", but, please, not in this situation - we want to simplify of our work!

2. Sometimes temporary tables have extra fields which are not returned in the recordset but help us to populate the returned fields - for example, with "UPDATE #recordset..." statement which follows the main "INSERT #recordset...". It's not bad to mark both fields' groups with appropriate comments (something like "Fields to be returned in recordset" and "Fields for internal use"):

Code: Select all

CREATE TABLE #recordset (
     /*********** Fields to be returned in recordset: ***********/
     start_date             datetime         null
    ,end_date               datetime         null
    ,certificate            varchar(200)     null
    ,company                varchar(200)     null
    ,date_taken             datetime         null
    ,comments               varchar(200)     null
    ,class_description      varchar(100) not null
    ,class_code             varchar(50)      null
    ,recertification_date   datetime         null /* recertification_period passed after date_taken */
     /*********** Fields for internal use: ***********/
    ,student_id             integer      not null
    ,sc_id                  integer          null
    ,recertification_period tinyint          null /* needed to calculate recertification_date */)

Advantages:

1. Easier to make sure all the returned fields are populated;

2. You have the full description of the recordset, returned by the proc's, including fields' data types and length. Looking at the final SELECT in the end of the proc, you see only fields names and order, but not data types and length.