有的人用字串分隔技巧送出一个很长的字串,到了后端资料库时再想办法分割字串。我则是使用送出xml格式字串的方式,让后端DB转换成表格之后就可以做多种运用了。
这次的例子如下:
<Data>
<Item userName="Kenny" age="18" level="9" />
<Item userName="DS" age="180" level="99" />
<Item userName="BL" age="180" level="99" />
.....
</Data>
后端的Stored Procedures写成这样:
ALTER PROCEDURE [dbo].[TestXmlSproc]
@data xml
AS
BEGIN
SET NOCOUNT ON;
Declare @table Table (
rowID int identity,
userName varchar(20),
age int,
level int
)
Insert Into @table
Select Tbl.Col.value('@userName', 'varchar(20)'),
Tbl.Col.value('@age', 'int'),
Tbl.Col.value('@level', 'int')
From @data.nodes('//Data/Item') Tbl(Col)
写下来你就可以自由运用@table这个暂存资料表了。下面是我利用迴圈做更进阶的使用
ALTER PROCEDURE [dbo].[TestXmlSproc]
@data xml --接收传入的xml格式字串
AS
BEGIN
SET NOCOUNT ON;
Declare @table Table (
rowID int identity,
userName varchar(20),
age int,
level int)
Declare @intRowCounts int,
@intRowIndex int,
@intNewValue int,
@intOriginalValue int
Set @intRowIndex = 1
--把xml格式字串转成资料表
Insert Into @table
Select Tbl.Col.value('@userName', 'varchar(20)'),
Tbl.Col.value('@age', 'int'),
Tbl.Col.value('@level', 'int')
From @data.nodes('//Data/Item') Tbl(Col)
--取得total的row数量
Select @intRowCounts = Count(RowId)
From @table
While (@intRowIndex <= @intRowCounts)
Begin
--取得新输入的数值
Select @intNewValue = level
From @table
Where rowID = @intRowIndex
--取得原始的数值
Select @intOriginalValue = level
From Member
Where userName Like (
Select userName
From @table
Where rowID = @intRowIndex)
--如果数值有变动则更新数值
If (@intOriginalValue != @intNewValue)
Begin
Update Member
Set level = @intNewValue
Where userName Like (
Select userName
From @table
Where rowID = @intRowIndex)
End
Set @intNewValue = 0
Set @intOriginalValue = 0
--记得更新index
Set @int_RowIndex = @int_RowIndex + 1
End
|