Loop with sql through a list of values and execute some functions like send an email via a mail function 01.10.19

DECLARE @list TABLE (Value NVARCHAR(50))
INSERT INTO @list VALUES ('stock_work_process_components')
INSERT INTO @list VALUES ('stock_work_process_silver')
INSERT INTO @list VALUES ('stock_work_process_gold')
INSERT INTO @list VALUES ('stock_work_process_steel')
INSERT INTO @list VALUES ('stock_work_process_brass')
INSERT INTO @list VALUES ('stock_goods_ready_to_ship')
INSERT INTO @list VALUES ('stock_stock8_molds_and_rubbers')

DECLARE @value VARCHAR(50)

DECLARE db_cursor CURSOR FOR  
SELECT Value FROM @list
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @value

WHILE @@FETCH_STATUS = 0   
BEGIN   
 PRINT @value

declare @sql nvarchar(1000)  = 'DECLARE @id INT  = (SELECT TOP 1 id FROM SL_h.dbo.reports  WHERE  CONVERT(VARCHAR(8),date, 112) = CONVERT(VARCHAR(8), GETDATE() -2, 112) AND '+ @value +' = 0); IF @id IS NOT NULL BEGIN exec sl_h.dbo.mail  "ALERT! Report ID  '+ @value  +' missing report",@id  END'
  EXEC(@sql)
  FETCH NEXT FROM db_cursor INTO @value   
END

CLOSE db_cursor   
DEALLOCATE db_cursor

mail function:

USE [SL_h]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 ALTER  procedure [dbo].[mail]
@subject as nvarchar(max)
,@body as nvarchar(max)

DECLARE @profile_name nvarchar(max)= 'Mail'
DECLARE @recipients  nvarchar(max)  = N'foo@bar.com'

EXEC msdb.dbo.sp_send_dbmail
  @profile_name = @profile_name,
  @recipients = @recipients,
  @body = @body,
  @body_format = 'HTML',
  @subject = @subject

https://social.msdn.microsoft.com/Forums/en-US/155d8dcf-03a4-4b68-88db-f1ce49f5170a/how-do-i-use-a-variable-to-specify-the-column-name-in-a-select-statement

Anonymous

Loop with sql through a list of values and execute some functions like send an email via a mail function 01.10.19

Information Epoch 1788949466