jump to navigation

new features sql server 2008-user defined table type and table valued parameters March 10, 2008

Posted by fofo in SQL Server 2008.
Tags: ,
trackback

In Sql Server 2008 you can pass a table variable in a stored procedure as a parameter. now you have the ability to send multiple rows of data in a stored procedure.one main advantage of that is that it will reduce the amount of round trips to the server. 

we are going to walk through the following example that explains the new features in sql server 2008. we can create a sample database. for example

create database sqlserver2008sample

let’s create a table- a customers table

CREATE TABLE [dbo].[Customers](

[Cust_ID] [int] NOT NULL,

[Cust_Name] [varchar](50) NOT NULL,

[Cust_Surname] [varchar](50) NOT NULL,

[Cust_Email] [varchar](50) NOT NULL)

we must insert some values in the table. we can do that by using a stored procedure

create procedure insertintocustomer(@Cust_ID int,@Cust_Name varchar(50),@Cust_Surname varchar(50),@Cust_Email varchar(50))

as

begin

insert into customers

values(

@Cust_ID, @Cust_Name,@Cust_Surname,@Cust_Email)

end

in order to insert values in the table we must execute that stored procedure multiple times. for example

execute insertintocustomer 1,‘robbie’,‘fowler’,‘rb@liverpool.com’

execute insertintocustomer 2,‘michael’,‘owen’,‘mo@liverpool.com’

now lets do the same by inserting table value parameters. 

first we create a user defined table data type.

create type customertype as table

(Cust_ID int,Cust_Name varchar(50),Cust_Surname varchar(50),Cust_Email varchar(50))

then we create the new stored procedure that takes the table type as a parameter.

create procedure newcustomer(@Customer_details customertype READONLY)

as

begin

insert into customers

select * from @Customer_details

end

lets create a variable of table data type

declare @customers customertype

lets fill the table variable by using insert statements

insert into @customers values (1,‘steven’,‘gerrard’,‘sg@liverpool.com’)

insert into @customers values (2,‘jamie’,‘caragher’,‘jc@liverpool.com’)

now we can execute the stored procedure by passing the table value parameter- @customers

execute newcustomer @Customers

if we now try a select statement in the customers table we will see the new values added.

hope it helps. 

Comments»

1. mb - July 11, 2008

this is pretty cool.

however, I’d like to see an example of calling your newcustomer from code, without it, nothing is really solved as you still need to pass data to sql one record at a time or via xml and parsing to this temp table, or by some of the other hacks I have seen.

2. mb - July 14, 2008
3. Dutt - November 6, 2008

50 New Features of SQL Server 2008 at Microsoft .NET Support
http://msdotnetsupport.blogspot.com/2008/11/sql-server-2008-new-features.html

4. Ramlakhan - August 9, 2010

sUPERB YAR

5. Min - September 1, 2010

Excellent article

6. Syed Shakeer Hussain - March 19, 2011

Explanation is very good.

7. arun - April 15, 2011

iam creating a type like this

create type customertype as table

(Cust_ID int,Cust_Name varchar(50),Cust_Surname varchar(50),Cust_Email varchar(50))

but its giving following Error :
Incorrect syntax near the keyword ‘as’.

Andy - May 19, 2011

This worked fine in sql 2008 for me, word for word – and is definitely the correct syntax. Are you in the right context?
Try
Use [DatabaseName]
first…
or simply
create type [databasename].customertype as table … etc

8. Bharathi - May 23, 2011

We can create default constraints in user defined table type in sql server 2008?


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: