TSQL,Help,programmers,easily,build,insertupda
Quick Search for:  in language:    
TSQL,Help,programmers,easily,build,insertupda
   Code/Articles » |  Newest/Best » |  Community » |  Jobs » |  Other » |  Goto » | 
CategoriesSearch Newest CodeCoding ContestCode of the DayAsk A ProJobsUpload
SQL Stats

 Code: 31,327 lines
 Jobs: 372 postings

 
Sponsored by:

 

You are in:

 
Login



Latest Code Ticker for SQL.
Unix Date Convertor Function
By George Graff on 10/23


Insert pipe delimited rows into multiple rows.
By Charles Toepfer on 10/21


Logfiles by PL/SQL
By Stephan Rechberger on 10/21


To display a name in a default language if the given one doesn't exist
By Serge Alard on 10/18


Order by column except a few values
By Serge Alard on 10/18


Introduction to PL/SQL (Series 3) Cursors
By David Nishimoto on 10/14


Sorting a String using T-SQL
By Gaurav Pugalia on 10/12


Protecting against TSQL virii, worms and time bombs
By Joseph Gama on 10/11


Click here to see a screenshot of this code!Get size in bytes of SP, View, Trigger, UDF or Rule
By Joseph Gama on 10/11

(Screen Shot)

Click here to put this ticker on your site!


Add this ticker to your desktop!


Daily Code Email
To join the 'Code of the Day' Mailing List click here!





Affiliate Sites



 
 
   

ColDefinition or helping upsert SP writing

Print
Email
 

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

Users have accessed this article 640 times.
 

(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.
sp_ColDefinition

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

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

 
Report Bad Submission
Use this form to notify us if this entry should be deleted (i.e contains no code, is a virus, etc.).
Reason:
 
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 AM:Anton Damhuis
Since you are using functions, it is not SQL 7 , nor SQL 6.5 and earlyer compatable. Otherwise it looks good.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
9/27/2002 1:34:26 PM:wiwiz
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.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
Add Your Feedback!
Note:Not only will your feedback be posted, but an email will be sent to the code's author in your name.

NOTICE: The author of this article has been kind enough to share it with you.  If you have a criticism, please state it politely or it will be deleted.

For feedback not related to this particular article, please click here.
 
Name:
Comment:

 

Categories | Articles and Tutorials | Advanced Search | Recommended Reading | Upload | Newest Code | Code of the Month | Code of the Day | All Time Hall of Fame | Coding Contest | Search for a job | Post a Job | Ask a Pro Discussion Forum | Live Chat | Feedback | Customize | SQL Home | Site Home | Other Sites | About the Site | Feedback | Link to the Site | Awards | Advertising | Privacy

Copyright© 1997 by Exhedra Solutions, Inc. All Rights Reserved.  By using this site you agree to its Terms and Conditions.  Planet Source Code (tm) and the phrase "Dream It. Code It" (tm) are trademarks of Exhedra Solutions, Inc.