如何在执行存储过程时从GoalSetUp表获取Goal值？

当然可以实现！这正是你创建GoalSetUp表的核心价值所在——统一管理配置，避免逐个修改存储过程的繁琐工作。下面是具体的修改步骤和代码示例：

核心修改思路 #

我们需要移除原来硬编码的SET @Goal= '15'，替换为从GoalSetUp表中根据当前的@VC（DeptCode）和@ap（OpCode）查询对应的Goal值，同时处理没有匹配记录的边界情况，避免存储过程出错。

具体代码修改 #

1. 替换硬编码的Goal赋值 #

找到存储过程中这一段硬编码赋值的代码：

SET @VC = 'VC' 
SET @ap = 'ap'
SET @Goal= '15' 

将其替换为以下从表中查询的逻辑：

SET @VC = 'VC' 
SET @ap = 'ap'

-- 从GoalSetUp表匹配DeptCode和OpCode，获取对应的Goal值
SELECT @Goal = CAST(Goal AS VARCHAR(10))
FROM GoalSetUp
WHERE DeptCode = @VC 
  AND OpCode = @ap

-- 处理未找到匹配记录的情况：可选择设置默认值或抛出错误
IF @Goal IS NULL
BEGIN
    -- 方案1：设置默认值（沿用原来的15）
    SET @Goal = '15'
    -- 方案2：抛出错误提醒（如果不允许无配置的情况）
    -- RAISERROR('未找到DeptCode=%s、OpCode=%s对应的Goal配置', 16, 1, @VC, @ap)
END

2. 完整修改后的存储过程 #

以下是修改后的完整存储过程代码，确保逻辑连贯可直接使用：

DECLARE @Offset INT , @Start DATETIME , @End DATETIME , @VC CHAR(2) , @ap CHAR(2) ,@Goal VARCHAR(10) 
--------------------------------------------------- 
SET @VC = 'VC' 
SET @ap = 'ap'

-- 从GoalSetUp表获取对应的Goal值
SELECT @Goal = CAST(Goal AS VARCHAR(10))
FROM GoalSetUp
WHERE DeptCode = @VC 
  AND OpCode = @ap

-- 处理无匹配的情况，避免变量为空导致后续计算错误
IF @Goal IS NULL
BEGIN
    SET @Goal = '15' -- 默认值，可根据实际需求调整
END

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字段，建议给DeptCode+OpCode创建唯一索引，避免重复配置导致查询返回多条数据。
• 数据类型匹配：如果GoalSetUp表的Goal字段是数值类型（比如INT），用CAST(Goal AS VARCHAR(10))转换为和原变量一致的类型，避免类型错误。
• 扩展性：其他同类型的存储过程都可以复用这个逻辑，以后只需要修改GoalSetUp表的数据，无需逐个调整存储过程，大大提升维护效率。

内容的提问来源于stack exchange，提问作者DRUIDRUID