34,838
社区成员




EXECUTE master.dbo.xp_create_subdir N'd:\database_bak\'
USE [master]
GO
/****** Object: StoredProcedure [dbo].[ADDUSER] Script Date: 08/06/2018 14:10:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE ADDFileZi
as
if not object_id(N'Tempdb..#T3') is null
drop table #T3
Go
create table #T3([id] int,[name] nvarchar(24),[father] int)
Insert #T3
select * from OPENQUERY
(MYSQL_OA_DB,'select id,name,father from toa_department')
declare @departmentname varchar(50),@departmentnamezi varchar(50),@sqldepartmentnamezi varchar(100)
-- 声明游标
DECLARE C_ADDFilezi CURSOR FAST_FORWARD FOR
--查询出
Select a.name,b.name as namezi from #T3 a JOIN #T3 b ON a.id = b.father
OPEN C_ADDFilezi;
-- 取第一条记录
FETCH NEXT FROM C_ADDFilezi INTO @departmentname,@departmentnamezi;
WHILE @@FETCH_STATUS=0
BEGIN
set @sqldepartmentnamezi='md d:\HelensFile\'+@departmentname+'\'+@departmentnamezi+''
exec xp_cmdshell @sqldepartmentnamezi
-- 取下一条记录
FETCH NEXT FROM C_ADDFilezi INTO @departmentname,@departmentnamezi;
END
-- 关闭游标
CLOSE C_ADDFilezi;
-- 释放游标
DEALLOCATE C_ADDFilezi;
WITH cte AS (
Select id,NULL AS fid,name AS fname,NAME from #tab WHERE id=25
UNION ALL
SELECT b.id,a.id,a.name,b.name
FROM cte AS a
INNER JOIN #tab AS b ON a.id=b.father
)
SELECT id,fid,fname,name FROM cte
id fid fname name
----------- ----------- ------------------------ ------------------------
25 NULL 运营中心 运营中心
107 25 运营中心 上海
106 25 运营中心 武汉
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[name] nvarchar(24),[date] Date,[father] int,[uid] int,[unionid] int)
Insert #T
select 25,N'运营中心','2017-10-01',801,1,1 union all
select 107,N'上海','2017-10-01',25,1,1 union all
select 106,N'武汉','2017-10-01',25,1,1
Go
--测试数据结束
Select a.id,a.name,b.name from #T a JOIN #T b ON a.id = b.father