我有几个存储过程，每天/每周/每月运行一次，以HTML格式通过电子邮件发送结果集。我在每个值上声明一个值作为除以的"目标"。这很好用，但是当我们需要更改每个"目标"的值时，这非常耗时。我想将所有值存储在一个表中，并让 SP 调用该表以获取"目标"的值。我正在展示其中一个 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'
GoalSetUp表
DeptCode OpCode Goal Location
VC ap 15 10
有没有办法调用GoalSetUp表来检索 SP 中使用的GoalFor@Goal值的值？
SET @Goal= (select Goal from GoalSetUp where /*conditions returning one row here*/)
必须编辑存储过程才能为@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)