---------------------------------------- -- ---------------------------------------- --------------------

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
as
   if @title_id is null
      select * from dbo.titles
   else
      select * from dbo.titles where title_id = @title_id
go
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
as
   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
   else
      insert into dbo.titles(title_id,title,type,pub_id,
      price,advance,royalty,ytd_sales,notes,pubdate) values(@title_id,@title,@type,@pub_id,
      @price,@advance,@royalty,@ytd_sales,@notes,@pubdate)

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 :

DECLARE
---------------------
@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
GO

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]
GO
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]
GO

CREATE function fn_SizePrecScale(
@Type varchar(50),
@Length int,
@Prec int,
@Scale int)
returns varchar(100)
as
begin
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) + ']'
ELSE
SET @RC = @Type + '(' + cast(@Length as varchar) + ')'

RETURN(@RC)
end

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

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

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


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