Important alert: (current site time 5/29/2015 1:40:47 PM EDT)


ColDefinition or helping upsert SP writing

Submitted on: 9/26/2002 11:22:42 PM
By: Pollus Brodeur 
Level: Intermediate
User Rating: Unrated
Compatibility: SQL Server 2000, SQL Server 7.0, SQL Server 6.5 and earlier
Views: 12629
author picture
(About the author)
     Help programmers to easily build insert/update stored procedures. It will create almost all the TSQL code for you.

Terms of Agreement:   
By using this article, you agree to the following terms...   
  1. You may use this article in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
  2. You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.   
  3. You may link to this article from another website, but ONLY if it is not wrapped in a frame. 
  4. You will abide by any additional copyright restrictions which the author may have placed in the article or article's description.



Most of the stored procedures I was asked to write were simple select or insert/update queries. Letís see an example of those queries on the pubs database.

This is a simple SELECT query

create proc titlesSelect
   @title_id tid = null
   if @title_id is null
      select * from dbo.titles
      select * from dbo.titles where title_id = @title_id
create proc titlesUpsert
   @title_id tid,
   @title varchar(80),
   @type char(12),
   @pub_id char(4),
   @price money,
   @advance money,
   @royalty int,
   @ytd_sales int,
   @notes varchar(200),
   @pubdate datetime
   if exists(select * from dbo.titles where title_id = @title_id)
      update dbo.titles
      set title = @title,
          type = @type,
          pub_id = @pub_id,
          price = @price,
          advance = @advance,
          royalty = @royalty,
          ytd_sales = @ytd_sales,
          notes = @notes,
          pubdate = @pubdate
      where title_id = @title_id
      insert into dbo.titles(title_id,title,type,pub_id,
      price,advance,royalty,ytd_sales,notes,pubdate) values(@title_id,@title,@type,@pub_id,

Those queries were easy to write but it took me a lot of time, especially the upsert one (upsert is a merge of insert and update) because I had to find the associated columns types for each variables for parameters declaration. Then write all the affectations in the update. Then all the insert into columns and then type the variables in the values enumeration. Now think of me when I was told that we wanted all the tables to have the same 2 SP. I was almost unhappy ! I could have write a VB application that could create all this for me but I wanted a tool inside SQL to help me write those SP. So I came up with the sp_ColDefinition which is now stored in the master database of our developpement server. The main reason for me to put this in the master database was that I could call it like sp_help. That means in every database without specifying full name (master.dbo.sp_ColDefinition) and it was easy to be called using the Customize in Query Analyser.

Now this tool, this sp_ColDefinition what does it do and how can it help me ?

sp_ColDefinition will use the metadata tables to create a table result that looks like this :

@title_id varchar(6),
@title varchar(80),
@type char(12),
@pub_id char(4),
@price money,
@advance money,
@royalty int,
@ytd_sales int,
@notes varchar(200),
@pubdate datetime(8),

(10 row(s) affected)

This result is in text mode but when in grid mode, you can select the colunm and paste it in the editor. Now you can understand that this is the parameters declaration. but other columns will help you with :

INSERT INTO, VALUES, SET and even SELECT to get a table select into variables.

How can I install this great tool on my server and test it ?

All you have to do is get a copy of the script at the end of the document and copy this in your SQL Query Analyser and run it (use F5)

Now to try it you can type :

sp__ColDefinition {tablename}.

replace tablename with a table you need and don't forget to type 2 underscores between sp and ColDefinition.


use master

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp__ColDefinition]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp__ColDefinition]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_SizePrecScale]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_SizePrecScale]

CREATE function fn_SizePrecScale(
@Type varchar(50),
@Length int,
@Prec int,
@Scale int)
returns varchar(100)
DECLARE @RC varchar(100)

IF @Type in ('smalldatetime','datatime','text','image','bit','ntext','uniqueidentifier','bigint','int','smallint','tinyint','money','smallmoney')
SET @RC = @Type
ELSE IF @Type in('decimal','numeric')
SET @RC = @Type + '(' + cast(@Length as varchar) + ') [' + cast(@Prec as varchar) + ',' + cast(@Scale as varchar) + ']'
SET @RC = @Type + '(' + cast(@Length as varchar) + ')'

CREATE PROC dbo.sp__ColDefinition
@ObjectName sysname
DECLARE @xtype char(2)

SELECT @xtype = xtype FROM sysobjects WHERE name = @ObjectName

IF @xtype not in('U','FN','V','P')
RAISERROR ('L''objet n''existe pas dans le catalogue', 16, 1)

IF @xtype in ('U','V')
[DECLARE] = '@' + + ' ' + dbo.fn_SizePrecScale(,C.length,C.xprec,C.xscale) + ',', + ',' AS [INSERT INTO],
'@' + + ',' AS [VALUES], + ' = @' + + ',' AS [SET],
'@' + + ' = ' + + ',' AS [SELECT]
from sysobjects O, syscolumns C, systypes T
where = and C.xtype = T.xtype
and = @ObjectName and T.xtype = T.xusertype
order by C.colorder
[DECLARE] = + ' ' + dbo.fn_SizePrecScale(,C.length,C.xprec,C.xscale) + ','
from sysobjects O, syscolumns C, systypes T
where = and C.xtype = T.xtype
and = @ObjectName and T.xtype = T.xusertype
order by C.colorder

Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this article (in the Intermediate category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)

Other User Comments

9/27/2002 3:26:34 AMAnton Damhuis

Since you are using functions, it is not SQL 7 , nor SQL 6.5 and earlyer compatable.
Otherwise it looks good.
(If this comment was disrespectful, please report it.)

9/27/2002 1:34:26 PMwiwiz

That's right ! It will not work with with SQL 7 or 6.5. The function still can be placed in the procedure as a computed column. Guest I make a second version soon.
(If this comment was disrespectful, please report it.)

12/29/2002 2:09:58 PM

Hi, Please help me with creating a Stored Procedure for Autogeneration of Customer ID.
My email address is
Thanks for you help in advance.
(If this comment was disrespectful, please report it.)

5/15/2008 8:53:33 AMMark Huber

This is great, thanks
(If this comment was disrespectful, please report it.)


Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular article, please click here instead.)

To post feedback, first please login.