创建如下函数

创建函数[dbo].[fn_Split](@ text nvarchar(max)，@delimiter varchar(100)='''')
返回@Strings TABLE
(
position int IDENTITY主键，
值nvarchar(max)
)
AS
开始
DECLARE @index int
SET @index = -1
WHILE(LEN(@text)> 0)
开始
SET @index = CHARINDEX(@delimiter，@text)
IF(@index = 0)AND(LEN(@text)> 0)
开始
插入@Strings值(@text)
BREAK
结束
IF(@index> 1)
开始
插入@Strings值(LEFT(@text，@index-1))
SET @text = RIGHT(@text，(LEN(@text)-@index))
结束
ELSE
SET @text = RIGHT(@text，(LEN(@text)-@index))
END
返回
END





现在，您可以按以下步骤在您的存储过程中使用此功能


-========================================= -作者:< Amit ,, Amit>
-创建日期:< 2011年4月23日，<
-说明:<选择系统值，>
-========================================= 创建过程[dbo].[SP_UpdateEmployee]
-在此处添加存储过程的参数

@strpersonphonepar text = Null，

AS
开始
声明@items表
(
位置int，
值varchar(max)
)
声明@itemvalues表
(
位置int，
值varchar(max)
)
声明@EmployeeIdpar varchar(max)
声明@PersonIdpar varchar(max)


声明@IsActivepar varchar(max)
声明@CreatedBypar varchar(max)
声明@PersonEMail varchar(max)
声明@PhoneTypeIdpar varchar(max)
声明@PersonPhoneNumberpar varchar(max)
声明@itemvalue varchar(max)



----------插入个人电话的详细信息开始
插入@items
从 fn_split(@strpersonphonepar，''='')
中选择位置和值 while((从@items选择count(*))> 0)
开始
设置@itemvalue =(从@items中选择前1个值)
插入@itemvalues
从fn_split(@itemvalue，''，'')中选择位置，值

设置@PhoneTypeIdpar =(从@itemvalues中选择位置= 1的值)
设置@PersonPhoneNumberpar =(从@itemvalues中选择位置= 2的值)
设置@IsActivepar =(从@itemvalues中选择位置= 3的值)
设置@CreatedBypar =(从@itemvalues中选择位置= 4的值)
设置@IDCheckNullpar =(从@itemvalues中选择位置= 5的值)
IF(@IDCheckNullpar IS NULL)或(@IDCheckNullpar =``NULL'')
开始
插入到PersonPhone(PersonId，PhoneTypeId，PersonPhoneNumber，
IsActive，CreatedBy，CreatedDate)
VALUES(@ PersonId，@ PhoneTypeIdpar，@ PersonPhoneNumberpar，
@ IsActivepar，@ CreatedBypar，GETDATE())
结束

ELSE

开始

更新PersonPhone设置PhoneTypeId = @PhoneTypeIdpar，PersonPhoneNumber = @PersonPhoneNumberpar，
IsActive = @IsActivepar，LastModifiedBy = @ CreatedBypar，LastModifiedDate = GETDATE()

在哪里PersonPhoneId = @IDCheckNullpar

END

从@Itemvalues中删除
从@items中删除，其中value = @itemvalue
结束

----------插入个人电话的详细信息

END

Create a Function as below

CREATE FUNCTION [dbo].[fn_Split](@text nvarchar(max), @delimiter varchar(100) = '' '')
RETURNS @Strings TABLE
(
position int IDENTITY PRIMARY KEY,
value nvarchar(max)
)
AS
BEGIN
DECLARE @index int
SET @index = -1
WHILE (LEN(@text) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter , @text)
IF (@index = 0) AND (LEN(@text) > 0)
BEGIN
INSERT INTO @Strings VALUES (@text)
BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
ELSE
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
RETURN
END





NOW u can use this function in ur store proc as follows


-- =============================================
-- Author: <Amit,,Amit>
-- Create date: <April 23,2011,,>
-- Description: <Select System Vales,,>
-- =============================================
CREATE PROCEDURE [dbo].[SP_UpdateEmployee]
-- Add the parameters for the stored procedure here

@strpersonphonepar text = Null,

AS
BEGIN
declare @items table
(
position int ,
value varchar(max)
)
declare @itemvalues table
(
position int ,
value varchar(max)
)
declare @EmployeeIdpar varchar(max)
declare @PersonIdpar varchar(max)


declare @IsActivepar varchar(max)
declare @CreatedBypar varchar(max)
declare @PersonEMail varchar(max)
declare @PhoneTypeIdpar varchar(max)
declare @PersonPhoneNumberpar varchar(max)
declare @itemvalue varchar(max)



----------INSERT PERSON PHONE DETAILS STARTS
insert into @items
select position,value from fn_split( @strpersonphonepar,''='')
while((select count(*) from @items)>0)
begin
set @itemvalue = (select top 1 value from @items)
insert into @itemvalues
select position,value from fn_split(@itemvalue,'','')

set @PhoneTypeIdpar = (select value from @itemvalues where position = 1)
set @PersonPhoneNumberpar =(select value from @itemvalues where position = 2)
set @IsActivepar =(select value from @itemvalues where position = 3)
set @CreatedBypar =(select value from @itemvalues where position = 4)
set @IDCheckNullpar =(select value from @itemvalues where position = 5)
IF (@IDCheckNullpar IS NULL) OR (@IDCheckNullpar = ''NULL'')
BEGIN
INSERT INTO PersonPhone(PersonId,PhoneTypeId, PersonPhoneNumber,
IsActive,CreatedBy,CreatedDate)
VALUES(@PersonId, @PhoneTypeIdpar, @PersonPhoneNumberpar,
@IsActivepar, @CreatedBypar, GETDATE())
END

ELSE

BEGIN

UPDATE PersonPhone SET PhoneTypeId = @PhoneTypeIdpar, PersonPhoneNumber = @PersonPhoneNumberpar,
IsActive = @IsActivepar, LastModifiedBy = @CreatedBypar,LastModifiedDate = GETDATE()

WHERE PersonPhoneId = @IDCheckNullpar

END

delete from @Itemvalues
delete from @items where value = @itemvalue
end

----------INSERT PERSON PHONE DETAILS ENDS

END