ITPub博客

首页 > Linux操作系统 > Linux操作系统 > User defined table type and table valued parameters

User defined table type and table valued parameters

原创 Linux操作系统 作者:magicgao8888 时间:2009-06-02 17:12:00 0 删除 编辑
********************************************************************************************************************
*********************How to use user defined table type and table valued parameters?********************
【Overview】
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 in sql server 2008. we can create a sample
database.
【For example】
1. 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
 )
-------------------------------------------------------------------------------------------------
2. 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
-------------------------------------------------------------------------------------------------
3. 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’
-------------------------------------------------------------------------------------------------
4. 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)
 )
-------------------------------------------------------------------------------------------------
5. 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
-------------------------------------------------------------------------------------------------
6. Lets create a variable of table data type. Now we can execute the stored procedure by passing the table
value parameter- @customers
-------------------------------------------------------------------------------------------------
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
------------------------------------------------------------------------------------------------
8. If we now try a select statement in the customers table we will see the new values added.
    Hope it helps.

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14321372/viewspace-604928/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-03-10

  • 博文量
    35
  • 访问量
    73369