SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
----将DCM中的系统用户资料同步到WebOrder用户表
ALTER PROCEDURE [dbo].[SP_WO_SYNC_SYS_USERS]
(
@IsFirst int, --是否是第一次导入
@WebOrderDBName nvarchar(50) --WebOrderDB的名称
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql nvarchar(4000)
DECLARE @Name nvarchar(50)
DECLARE @FullName nvarchar(50)
DECLARE @WoPassword nvarchar(50)
DECLARE @Enabled nvarchar(50)
DECLARE @Attribute1_Output nvarchar(50)
DECLARE @Domain nvarchar(50)
DECLARE @Email nvarchar(50)
DECLARE @Phone nvarchar(50)
DECLARE @DefaultURL nvarchar(50)
DECLARE @CreateUser nvarchar(50)
DECLARE @Active nvarchar(50)
DECLARE @Attribute1 nvarchar(50)
DECLARE @Attribute2 nvarchar(50)
DECLARE @Attribute3 nvarchar(50)
SET @Domain ='Ariston'
SET @Email = ''
SET @Phone =''
SET @CreateUser = 'admin'
SET @DefaultURL =''
SET @Active = '0'
SET @Attribute1 ='0'
SET @Attribute2 ='同步用户'
SET @Attribute3 =''
BEGIN
--将DCM用户表中的用户导入到WebOrder用户表
SET @sql = 'DECLARE CustCurosr CURSOR FOR SELECT RTRIM(Name),RTRIM(FullName),RTRIM(Password),RTRIM(UPPER(Enabled)) from tbl_sysuser '
IF @IsFirst = 1
SET @sql = @sql + ' where LastUpdateTime = ''1900-01-01'' '
ELSE
SET @sql = @sql + ' where LastUpdateTime>dateadd(day,-2,getdate()) and LastUpdateTime<dateadd(day,1,getdate()) '
EXECUTE sp_executesql @sql
OPEN CustCurosr
FETCH NEXT FROM CustCurosr
INTO @Name,@FullName,@WoPassword,@Enabled
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql =N'SELECT @Attribute1_Out=Attribute1 FROM ' + @WebOrderDBName + '..WSCUser WHERE ACCOUNT=''' + @Name + ''' '
EXECUTE sp_executesql @sql,N'@Attribute1_Out nvarchar(50) output',@Attribute1_Output output
IF @@rowcount = 0
BEGIN
--INSERT DATA
IF @Enabled='TRUE'
BEGIN
SET @Attribute3 = convert(nvarchar(50),getdate(),121)
SET @sql='INSERT INTO ' + @WebOrderDBName + '..[WSCUser]
([Account]
,[Domain]
,[EmpNo]
,[Email]
,[Phone]
,[Description]
,[Password]
,[LanguageID]
,[DefaultURL]
,[Active]
,[LogonTimes]
,[LastLogonTime]
,[CreateTime]
,[CreateUser]
,[Attribute1]
,[Attribute2]
,[Attribute3])
VALUES
(''' + @Name + '''
,''' + @Domain + '''
,''' + @Name + '''
,''' + @Email + '''
,''' + @Phone + '''
,''' + @FullName + '''
,''' + @WoPassword + '''
,2
,''' + @DefaultURL + '''
,1
,0
,getdate()
,getdate()
,''' + @CreateUser + '''
,''' + @Attribute1 + '''
,''' + @Attribute2 + '''
,''' + @Attribute3 + ''')'
EXECUTE sp_executesql @sql
END
END
ELSE IF @@rowcount = 1
BEGIN
SET @Attribute3 = convert(nvarchar(50),getdate(),121)
IF @Enabled='TRUE'
SET @Active = '1'
IF @Attribute1_Output <>'1' --如果已存在此客户用户
BEGIN
SET @sql='UPDATE ' + @WebOrderDBName + '..WSCUser SET Password=''' + @WoPassword + ''',[Active]=' + @Active + ',Attribute1=''' + @Attribute1 + ''',Attribute2=''' + @Attribute2 + ''',Attribute3=''' + @Attribute3 + ''' WHERE ACCOUNT=''' + @Name + ''' '--UPDATE DATA
EXECUTE sp_executesql @sql
END
END
ELSE
--Insert Into Error Log
BEGIN
SET @sql='INSERT INTO ' + @WebOrderDBName + '..[WOSytemLog]
([OID]
,[USERID]
,[LOGDATE]
,[LOGDESC]
,[OPERATION])
VALUES
(newid()
,''' + @CreateUser + '''
,getdate()
,''DCM存储过程SP_WO_SYNC_SYS_USERS出错,账号' + @Name + '的用户数据重复''
,''DCM系统用户同步'')'
EXECUTE sp_executesql @sql
END
FETCH NEXT FROM CustCurosr
INTO @Name,@FullName,@WoPassword,@Enabled
END
CLOSE CustCurosr
DEALLOCATE CustCurosr
END
END
SQL 存储过程示例01
最新推荐文章于 2024-10-12 22:28:01 发布