【SQL开源代码栏目提醒】:网学会员在SQL开源代码频道为大家收集整理了CreateTableandStoreProcedure.sql提供大家参考,希望对大家有所帮助!
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Employee]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employee]
GO
CREATE TABLE [dbo].[Employee] (
[EmployeeId] [int] NOT NULL ,
[FirstName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE PROC sp_UpdateXML @empdata nText
AS
DECLARE @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT,@empdata
--This code updates old data.
UPDATE Employee
SET
Employee.FirstName = XMLEmployee.FirstName,
Employee.LastName = XMLEmployee.LastName
FROM OPENXML(@hDoc, 'NewDataSet/Employee')
WITH (EmployeeId Integer, FirstName varchar(100), LastName varchar(100)) XMLEmployee
WHERE Employee.EmployeeId = XMLEmployee.EmployeeId
--This code inserts new data.
Insert Into Employee
SELECT EmployeeId, FirstName, LastName
FROM OPENXML (@hdoc, '/NewDataSet/Employee',1)
WITH (EmployeeId Integer, FirstName varchar(100), LastName varchar(100)) XMLEmployee
Where XMLEmployee.EmployeeId Not IN (Select EmployeeID from Employee)
EXEC sp_
xml_removedocument @hDoc
GO