通过存储过程查询结果保存为Execl到目录里

ruo40018293

于 2020-06-13 15:44:08 发布

阅读量298

点赞数

分类专栏：日常代码文章标签： sql

本文链接：https://blog.csdn.net/ruo40018293/article/details/106733251

版权

日常代码专栏收录该内容

22 篇文章 0 订阅

订阅专栏

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

ALTER PROC dbo.OutExecl
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @Server VARCHAR(20) ,
@User VARCHAR(20) ,
@Password VARCHAR(20) ,
@sqlcmd VARCHAR(500) ,
@path VARCHAR(255) ,
@date VARCHAR(255) ,
@filename VARCHAR(255);

-- 启用xp_cmdshell
EXEC master.sys.sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC master.sys.sp_configure 'xp_cmdshell', 1;
RECONFIGURE WITH OVERRIDE;

-------------------------判断路径是否存在-------------------------
SET @path = 'D:\DateExport\';
SET @date = CAST(DATEPART(YYYY, GETDATE()) AS VARCHAR) + '年'
+ CAST(DATEPART(MM, GETDATE()) AS VARCHAR) + '月'
+ CAST(DATEPART(DD, GETDATE()) AS VARCHAR) + '日';
SET @path = @path + @date;
SET @filename = 'KMS' + @date + '开单记录' + '.xlsx';
DECLARE @TEMP TABLE ( A INT, B INT, C INT ); --建立一个虚拟表用来判断文件夹是否存在
INSERT @TEMP
EXEC [master]..xp_fileexist @path;
IF NOT EXISTS ( SELECT *
FROM @TEMP
WHERE B = 1 )
BEGIN
--因为XP_CMDSHELL 函数的参数不允许使用变量拼接所以使用EXEC(SQL)的方法
DECLARE @EX NVARCHAR(255);
SET @EX = 'EXEC xp_cmdshell ''MKDIR ' + @path + '''';
EXEC(@EX);
END;

-------------------------------------------------------------------------------
SET @path = REPLACE(@path, '\', '/') + '/' + @filename;
SET @Server = '120.76.233.**';
SET @User = '';
SET @Password = '';

SET @sqlcmd = 'bcp ##tTable out ' + @path + ' -c -t'
--+ @Server +
+' -U ' + @User + ' -P ' + @Password;
SELECT *
INTO ##tTable
FROM ( '查询语句'
) AS a;

--生成excel
EXEC master..xp_cmdshell @sqlcmd;

DECLARE @rarFileName VARCHAR(200);
SET @rarFileName = REPLACE(@path, '.xlsx', '.rar');

----压缩excel为rar
--EXEC p_ZipFile @path, @rarFileName;

-- --无论成功与否,执行次数都+1
-- UPDATE dbo.ExportDataApply
-- SET executionTimes = executionTimes + 1
-- WHERE exportApplyID = @id;

----修改申请表信息
-- SET XACT_ABORT ON;
-- BEGIN TRAN tr;
-- BEGIN TRY
-- -----修改
-- DECLARE @count INT;
-- SELECT @count = COUNT(BillNo)
-- FROM ##tTable;

-- UPDATE dbo.ExportDataApply
-- SET applyState = 1 ,
-- Annex = CASE WHEN @count < 2 THEN ''
-- ELSE REPLACE(@fileName, '.xlsx', '.rar')
-- END ,
-- remarks = CASE WHEN @count < 2 THEN '数据为零'
-- WHEN @count > 50000 THEN '数据超过50000行'
-- ELSE ''
-- END ,
-- enclosure = @rarFileName
-- WHERE exportApplyID = @id;

-- COMMIT TRAN tr;
-- END TRY
-- BEGIN CATCH
-- ROLLBACK TRAN tr;
-- DECLARE @msg VARCHAR(200);
-- SET @msg = '数据处理过程出现异常，错误代码：'
-- + CONVERT(VARCHAR(10), ERROR_NUMBER()) + '，错误信息：'
-- + ERROR_MESSAGE();
-- RAISERROR(@msg, 16, 1) WITH NOWAIT;
-- END CATCH;
-- QuitWithRollback:
-- IF ( @@TRANCOUNT > 0 ) --未结束的事物个数，超时的时候没有执行到提交或回滚事物，这里判断下，然后进行回滚
-- BEGIN
-- ROLLBACK TRAN tr;
-- RAISERROR('处理数据过程超时，连接中断，请检查网络是否畅通！', 16, 1) WITH NOWAIT;
-- END;
-- */
SET XACT_ABORT OFF;
               --关闭xp_cmdshell
EXEC master.sys.sp_configure 'xp_cmdshell', 0;
RECONFIGURE WITH OVERRIDE;
EXEC master.sys.sp_configure 'show advanced options', 0;
RECONFIGURE WITH OVERRIDE;
DROP TABLE ##tTable;