记一次存储编写。
需求:根据主表ID,将用户与职务关联信息一一对应插入到子表中。
CREATE PROCEDURE UserAdd
-- Add the parameters for the stored procedure here
@Ptid varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @idx int;
declare @PT_ID varchar(50);
declare @OU_ID varchar(50);
declare @PT_ValidateDate datetime;
declare @PT_IsCurrent int;
declare @PT_EndValidateDate datetime;
declare @PU_ID varchar(50);
DECLARE @userTable TABLE (OU_ID VARCHAR(50),PT_ID varchar(50),PT_ValidateDate datetime,PT_IsCurrent int,PT_EndValidateDate datetime);
INSERT INTO @userTable
--将用户信息列表转成多行
-- 定位下标,要搜索字符串,被搜索字符串,开始位置
select 'OU_ID'=substring(PT_UserList,b.number,charindex(',',PT_UserList+',',b.number)-b.number),PT_ID,PT_ValidateDate,PT_IsCurrent,PT_EndValidateDate
fro