带有灵活参数的 EXEC 存储过程，用于“select count(*)..."并发送电子邮件 [英] EXEC stored procedure with flexible parameter for "select count(*)..." and sending an eMail

问题描述

我想写一个存储过程，当select count(*)值大于Menge值时发送电子邮件.

I want to write a stored procedure to send an e-mail when the select count(*) value is bigger than the value Menge.

由于更多表和/或where条件"的参数，应灵活使用此存储过程

This stored procedure should be used flexible due the params for more tables and/or "where criteria"

不幸的是，我收到此错误，但无法修复它:(

Unfortunately I get this error and I am not able to fix it :(

消息 245，级别 16，状态 1，过程 sp_eMail_Test3，第 23 行
将 varchar 值select count(*) from test where not [sys_completed] is null"转换为数据类型 int 时转换失败.

Msg 245, Level 16, State 1, Procedure sp_eMail_Test3, Line 23
Conversion failed when converting the varchar value 'select count(*) from test where not [sys_completed] is null' to data type int.

你能帮我吗?

我的存储过程:

create PROCEDURE [dbo].[sp_eMail_Test3]
 @recordCount as int = 0, 
 @FileN as varchar(max) =null,
 @DatSubj as varchar(20) =null,
 @DatFile as varchar(20) =null,
 @SQL_Count as varchar(max) =null,

 @MySQL as varchar(max)=null,
 @MyTable as varchar(max)=null,
 @MyWhere as varchar(max)=null,

 @Menge as int = 0,

 @eMail_TO varchar(max) =null,
 @eMail_Subject varchar(max) =null,
 @eMail_Body varchar(max) =null

AS
BEGIN
    SET NOCOUNT ON;     
    set @MySQL = 'select count(*) from ' +@MyTable + ' where ' + @MyWhere 
    set @SQL_Count = @MySQL 
    set @recordCount = convert(int, @SQL_Count ) -- <<--this is the error

    IF (@recordCount > @Menge)  
    begin           
        -- variablen zuweisung
        set @DatSubj =  CONVERT(varCHAR(20),  GETDATE() ,120) --datum fürs subject
        set @DatFile =  replace(convert(varchar, getdate(), 120), ':','_') --datum für filename
        set @FileN ='Eska_Report_' + @DatFile +'.csv' --filename

        EXEC msdb.dbo.sp_send_dbmail
            @body_format = 'HTML',
            @profile_name = 'testmailprofile',
            @recipients = @eMail_TO,         
            @subject =  @eMail_Subject ,
            @Body = @eMail_Body  ,
            @query = 'SET NOCOUNT ON;
                        select * from Test where sys_gueltig = ''Y'' 
                        and not sys_completed is null  ',
         @attach_query_result_as_file = 1       , 
         @query_attachment_filename= @FileN     , 
         @query_result_separator = ';'      ,
         @query_result_no_padding= 1,       
         @exclude_query_output =1,      
         @append_query_error = 1,   
         @query_result_header =1
    end    
END

我这样称呼SP

exec sp_eMail_Test3
    @Menge = 0,
    @eMail_TO = '[e-mail]' ,
    @eMail_Subject = 'test3 ',
    @eMail_Body = 'Hallo, das ist ein Test',
    @MyTable ='test'    ,
    @MyWhere = 'not [sys_completed] is null'

以后想在VBA中通过ADO conenct调用存储过程

In the future I want to call the stored procedure via ADO conenct in VBA

推荐答案

我觉得有必要告诉您，像这样将变量传递到动态字符串中会让您对 SQL 注入持开放态度.这是一种不好的做法，除非非常严格地控制，否则通常会受到反对.

I feel obliged to advise you that passing variables into dynamic strings like this leaves you open to SQL injections. This is a bad practice, and generally frowned upon unless very strictly controlled.

说...

将您的@MySQL 从 varchar 更改为 nvarchar.

Change your @MySQL from varchar to nvarchar.

然后尝试改变这个:

set @MySQL = 'select count(*) from ' +@MyTable + ' where ' + @MyWhere 
set @SQL_Count = @MySQL 
set @recordCount = convert(int, @SQL_Count ) -- <<--this is the error

为此:

set @MySQL = 'select @recordCount=count(2) from ' + @MyTable + ' where ' + @MyWhere 
exec sp_execute @MySQL, N'@recordCount int OUTPUT', @recordCount=@recordCount OUTPUT

这篇关于带有灵活参数的 EXEC 存储过程，用于“select count(*)..."并发送电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！