SQLServer查询数据生成文档邮件发送

 

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO


ALTER procedure [dbo].[proc_dzw159_CSV]
AS
BEGIN
    
    SET NOCOUNT ON

    --如果当天已经生成过，就不再继续
    DECLARE @d DATE
    SET @d = GETDATE()

    IF EXISTS(SELECT id FROM Report WHERE CreateDate = @d AND FileName LIKE '%dzw159%')
        RETURN

    DECLARE @sql  VARCHAR(8000),@u VARCHAR(200),@cmd VARCHAR(100)
    DECLARE @fileName  VARCHAR(100)
    DECLARE @path VARCHAR(200)

    PRINT @path
    --文件名
    SET @fileName='dzw159' + REPLACE(LEFT(CONVERT(CHAR(10),@d,120),10),'-','') + '.csv'
    SET @path = 'D:\PPReport\'+CONVERT(VARCHAR(8),@d,112)+'\'
    SET @cmd='mkdir '+ @path
    EXEC master..xp_cmdshell @cmd, NO_OUTPUT


    SET @sql = 'SELECT ''区域'',''PARTNET_NAME'',''PART'', ''DEF_INTANSIT'''
    + ' UNION ALL ' 
    + 'SELECT ''123'',''456'',FaultyPN,CONVERT(VARCHAR(50),count(0)) ' 
    + ' FROM table.dbo.tableQuery(NOLOCK) '
    + ' WHERE Date IS NULL '
    + ' AND Status IS NULL '
    + ' GROUP BY FaultyPN '
            
    --SET @u=' -c -t ~ -S"." -U"user" -P"password"'
    SET @u = '" -T -t"," -c'
    SET @sql = 'BCP "'+  @sql +'" queryout "'+ @path + @fileName + @u
    EXEC master..xp_cmdshell @sql

    INSERT INTO Report(FileName,FilePath) VALUES('dzw159' + @fileName+ '.csv',@path+'dzw159' + @fileName+ '.csv')

    
    --发送email
    
    --DECLARE @i INT,@count INT,@ID INT, @EMAIL VARCHAR(1000),@fname VARCHAR(400),@fpath VARCHAR(400)
    --DECLARE @ TABLE (SID INT IDENTITY,ID INT)
    
    --INSERT INTO @(ID) SELECT ID FROM Report WHERE Eflag=0
    --AND FileName  = @fileName
    --SET @I=1
    --SELECT @count=COUNT(0) FROM @

    --WHILE @i<=@count
    --BEGIN
    --    SELECT @ID=A.ID,@fname=FileName,@fpath=FilePath
    --    FROM Report A INNER JOIN @ B ON A.ID=B.ID AND B.SID=@i
    
    --    EXEC msdb.dbo.sp_send_dbmail 
    --    @profile_name='dzw159', 
    --    @recipients='110@qq.com',
    --    @subject=@fname, 
    --    @body=@fname
    --    ,@file_attachments=@fpath
    --    SELECT * FROM dbo.Report
    --    UPDATE Report SET Eflag=1 WHERE ID=@ID
        
    --    SET @I = @i + 1
    --END
    
    SET NOCOUNT OFF

END
GO