代码之家 › 专栏 › 技术社区 › DRUIDRUID

执行SP时从表中获取值

stored-procedures sql-server-2008 sql-server sql

DRUIDRUID · 技术社区 · 6 年前

我有几个每天/每周/每月运行的存储过程,这些存储过程以HTML格式发送电子邮件结果集。我在每个表上声明一个值,作为要除以的“目标”。这很有效,但当我们需要更改每个“目标”的值时,这非常耗时。我想将所有值存储在一个表中,并让SP调用该表以获取“Goal”的值。我以其中一个SP为例,还将显示我创建的表 GoalSetUp

DECLARE 
    @Offset    INT         ,
    @Start     DATETIME    ,
    @End       DATETIME    , 
    @VC        CHAR(2)     ,                                            
    @ap        CHAR(2)     ,@Goal       VARCHAR(10)      
    ---------------------------------------------------
    SET @VC = 'VC'                                      
    SET @ap = 'ap'SET @Goal= '15'                           
    SET @Offset    = 1
    SET @Start     = dateadd(day, datediff(day, 0, getdate()), -@Offset)
    SET @End       = dateadd(day, datediff(day, 0, getdate()), -@Offset)
--VC AP--
DECLARE @23 varchar(max)  DECLARE @Head23 varchar(max)   DECLARE @Tail23 varchar(max)
DECLARE @mailitem_id23 as int DECLARE @statusMsg23 as varchar(max)DECLARE @Error23 as varchar(max) DECLARE @Note23 as varchar(max)
SET NoCount On;
SET @mailitem_id23 = null SET @statusMsg23 = null SET @Error23 = null SET @Note23 = null SET @Tail23 = '</table></body></html>'; SET @Head23 = 
'<html><head>' + '<style>' +
'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:9pt;color:Black;} ' +
'</style>' + '</head>' + '<body><table cellpadding=0 cellspacing=0 border=0 width=100%>' + '<tr bgcolor=#808080>'+
'<td width=5%><b>Codes</b></td>'+'<td width=5%><b>TimeSUM</b></td>'+'<td width=5%><b>Units</b></td>'+'<td width=5%><b>UPH</b></td>'+'<td width=5%><b>Goal%</b></td>'+'<td width=5%><b>ID</b></td>'+'<td width=10%><b>Associate Name</b></td></tr>';
SELECT @23= (SELECT
 [TD] = (t.DeptCode + '-' +  t.OpCode)
,[TD] = right(convert(varchar(9),(sum(datediff(second,StartTime,FinishTime)) / 3600 )),3) + ':' + right('0' + convert(varchar(2),(sum(datediff(second,StartTime,FinishTime)) / 60) % 60 ),2) 
,[TD] = PARSENAME(convert(varchar,cast(sum(units) as money),1),2)
,[TD] = cast(isnull(sum(Units) / nullif(sum(datediff(minute,StartTime,FinishTime))*1.0,0),0.0)*60  as decimal(10,0))
,[TD] = isnull((convert(varchar(30),cast((isnull(sum(Units) / nullif(sum(datediff(minute,StartTime,FinishTime))*1.0,0),0.0)*60)/nullif(@Goal,0)*100 as decimal(10,0))) + '%'), ' ')
,[TD] = t.ID
,[TD] = UPPER(isnull(ai.FirstName + ' ','**********fix me') + isnull(ai.LastName,''))
FROM TimeLog t left join AssociateInfo ai on t.Id = ai.Id where t.EventDate >= @Start and t.EventDate <= @End and t.DeptCode = @VC and t.OpCode = @ap group by t.EventDate, t.id, t.DeptCode, t.OpCode, ai.FirstName, ai.LastName
ORDER BY cast(isnull(sum(Units) / nullif(sum(datediff(minute,StartTime,FinishTime))*1.0,0),0.0)*60  as decimal(10,0)) desc
FOR XML RAW('tr'), ELEMENTS)
SET @23 = REPLACE(@23, '_x0020_', SPACE(1))SET @23 = REPLACE(@23, '_x003D_', '=')SET @23 = REPLACE(@23, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')SET @23 = REPLACE(@23, '<TRRow>0</TRRow>', '')SET @23 = @Head23 + @23 + @Tail23
Select @23
-----------------------------------------------------------------
-----------------------------------------------------------------
--Email
DECLARE @Body VARCHAR(MAX)
SET     @Body = ISNULL(@23, ' ')  
EXEC msdb.dbo.sp_send_dbmail 
 @profile_name ='myprofile',
 @recipients = 'myemail@myemail.com',
 @subject = 'Email',
 @body = @Body,
 @body_format = 'HTML'

目标设置 桌子

 DeptCode   OpCode  Goal    Location
 VC         ap      15      10

有什么方法可以让我拜访 目标设置 要检索的值的表 Goal 对于 @Goal 在我的SP中使用的值?

2 回复 | 直到 6 年前

avb 6 年前

SET @Goal= (select Goal from GoalSetUp where /*conditions returning one row here*/)

B3S 6 年前

您必须编辑存储过程,以便为@Goal获取动态值。像这样的

DECLARE 
    @Offset    INT         ,
    @Start     DATETIME    ,
    @End       DATETIME    , 
    @VC        CHAR(2)     ,                                            
    @ap        CHAR(2)     ,
    @Goal      VARCHAR(10)      
    ---------------------------------------------------
    SET @VC = 'VC'                                      
    SET @ap = 'ap'
    SELECT @Goal= GOAL FROM GOALTable    --change this row                        
    SET @Offset    = 1
    SET @Start     = dateadd(day, datediff(day, 0, getdate()), -@Offset)
    SET @End       = dateadd(day, datediff(day, 0, getdate()), -@Offset)