Temp table in stored proc (SQL Server)

Share this topic:



Link to this posting

Postby 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. Fields in "CREATE TABLE #recordset...", "INSERT #recordset..." and the final accord of your symphony - the "SELECT ... FROM #recordset" statements should be 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 are speaking about simplifying 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.
User avatar
Ursego
Site Admin
 
Posts: 112
Joined: 19 Feb 2013, 20:33

Return to Ocean of Databases

Who is online

Users browsing this forum: No registered users and 1 guest


Temp table in stored proc (SQL Server)

Share this topic:


If you think that this site is not too bad, please LIKE it in Facebook. Thanks!





cron
free counters

eXTReMe Tracker