以下为两个完成同样功能的存储过程,但存储过程bbb 是 xml 参数。在客户端(客户端指数据库之外)程序访问数据库传值时,可以把客户端输入页面输入项的多个值按xml格式以
字符串的形式传到数据库端,这样客户端输入页面输入项怎么增减,调用存储过程bbb的参数都是一个xml格式的字符串,
同时相应的改写存储过程bbb 内部代码即可,不用更改其参数例表问题是,这样做之后,对数据库性能有何影响?
CREATE PROCEDURE aaa
(
@LeaseId char(15),
@Sequences int,
@BId int
}
AS
INSERT INTO LeaseRentList(
LeaseId = @LeaseId, Sequences = @LeaseId,
BId = @LeaseId
)
---------------------------------------------------CREATE PROCEDURE bbb
(
@RentList xml
}
AS INSERT INTO LeaseRentList(
LeaseId, Sequences,
BId
)
SELECT ContractId = t.c.value('@C9','char(15)'),
Sequences = t.c.value('@C1','tinyint'),
BId = t.c.value('@C10','int')
FROM @RentList.nodes('/RentList/Row') t(c)
LeaseId = @LeaseId, Sequences = @LeaseId,
BId = @LeaseId
) 语句有这样的写法,
INSERT INTO LeaseRentList(LeaseId ,Sequences ,BId ) SELECT
@LeaseId, @LeaseId,
@LeaseId
(
LeaseId, LeaseId,
LeaseId
)
values
(
@LeaseId, @LeaseId,
@LeaseId
)
客户端输入的可以增减,但是得需要将根据节点都增加到xml.nodes中。CREATE PROCEDURE bbb
(
@RentList xml
}
AS
begin
--可以根据需要增加多列,当value为空时可以插入空值。
INSERT INTO LeaseRentList(LeaseId, Sequences,BId )
SELECT isnull(t.c.value('@C9','char(15)'),''),
isnull(t.c.value('@C1','tinyint'),''),
isnull(t.c.value('@C10','int'),'')
FROM @RentList.nodes('/RentList/Row') t(c);
--or do this
--declare @idoc smallint;
--exec sp_xml_preparedocument @idoc output,@RentList
--insert into LeaseRentList(LeaseId, Sequences,BId )
--select * from
--openxml(@idoc,'/RentList/Row',1)
-- with(LeaseId varchar(15) '@C9'
-- ,Sequences tinyint '@C1'
-- ,BId int '@C10')x;
--exec sp_xml_removedocument @RentList;
end
go