THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

On the typeof() campaign trail

Given a table:

CREATE TABLE [person] (
  
[id] INT IDENTITY(1,1) ,
  
[name] VARCHAR(100) ,
  
[age] INT
);

How nice would it be to be able to do something like this inside your stored procedures?:

DECLARE @person TABLE (
  
[name] typeof([person].[name]) ,
  
[age] typeof([person].[age])
);

Or, perhaps, declare something to be of type:

CREATE TYPE [dbo].[person] AS TABLE (
  
[name]  typeof([person].[name]) ,
  
[age]   typeof([person].[age])
);

Maybe you could even wrap it all up as:

CREATE PROC InsertPeople
  
@person [dbo].[person] READONLY
AS
BEGIN
   INSERT  
[person]([name],[age])
  
SELECT  [name],[age]
  
FROM    @person;
END

You know what to do.

That is all!

Published Wednesday, August 10, 2011 10:25 AM by jamiet
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Ranga Narasimhan said:

Great idea! Reminds me when I encountered an issue where parameters ranging from varchar(10) to varchar(100) to char(10) (just to mention a few) were used in stored procs referencing a table column of char(2). I had to change several procs to use char(2) parameter. One more vote!

August 10, 2011 4:24 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement