使用用户定义的表类型参数将数据插入数据库的存储过程 [英] stored procedure for insert data into database using user defined table type parameters
问题描述
USE [Planning_Staff]
GO
/ ******对象:StoredProcedure [dbo]。[usp_InsertStaffPlaning]脚本日期:08/16/2014 18 :26:15 ****** /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER程序[dbo]。[usp_InsertStaffPlaning]
(@MeetingType MeetingType readonly,
USE [Planning_Staff]
GO
/****** Object: StoredProcedure [dbo].[usp_InsertStaffPlaning] Script Date: 08/16/2014 18:26:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[usp_InsertStaffPlaning]
(@MeetingType MeetingType readonly,
- 创建MeetingType以供使用
@ClientID ClientID只读,
@StaffID int,
@FromDate datetime,
@ToDate datetime
)
as
begin
select *从@MeetingType进入#tempMeetingType
从@ClientID选择*进入#tempClientID
插入到tbl_PlanningDetails
(StaffID,
FromDate,
ToDate,
MeetingType in(从#tempMeetingType中选择MeetingType),
ClientID in(从#tempClientID中选择ClientID))
drop tab le #tempMeetingType
drop table #teclClientID
end
--MeetingType is Created for use
@ClientID ClientID readonly,
@StaffID int,
@FromDate datetime,
@ToDate datetime
)
as
begin
select * into #tempMeetingType from @MeetingType
select * into #tempClientID from @ClientID
insert into tbl_PlanningDetails
(StaffID,
FromDate,
ToDate,
MeetingType in(select MeetingType from #tempMeetingType),
ClientID in(select ClientID from #tempClientID))
drop table #tempMeetingType
drop table #tempClientID
end
推荐答案
您是否阅读过MSDN上的帖子
http://msdn.microsoft.com/ en-us / library / bb510489(v = sql.105).aspx [ ^ ]
这是一个很好的例子http://channel9.msdn.com/Blogs/ashishjaiman/SQL-Server-2008-Table-valued-parameters [ ^ ]
Did you read the post on the MSDN
http://msdn.microsoft.com/en-us/library/bb510489(v=sql.105).aspx[^]
Here is a good example http://channel9.msdn.com/Blogs/ashishjaiman/SQL-Server-2008-Table-valued-parameters[^]
这篇关于使用用户定义的表类型参数将数据插入数据库的存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!