i'm writing a cursor to send emails when our stock level goes below a safety value. The cursor seems to work fine except i have a in my email subject i use a @sku variable which at the moment is concatenating all the sku's for each mail.
the first email is fine: subject - SKU0001 IS BELOW SAFTEY
the second email reads : subject - SKU00014SKU0002 BELOW SAFETY
the third will read : subject - SKU00014SKU0002SKU0003 BELOW SAFETY
here's the code:
SET @emailTo = 'email@example.com'
SET @emailSubject = 'Warning stock is below safety for : '
-- SELECT RECORDS THAT NEED TO BE EMAILED
DECLARE c1 CURSOR FOR
SELECT @emailTo as email_add,
rtrim(StockCode) as StockCode
WHERE email_status = 1
-- LOOP THROUGH RECORDSET AND SEND EMAIL FOR EACH RECORD
FETCH NEXT FROM c1 INTO @email, @sku
WHILE @@FETCH_STATUS <> -1
@emailSubject = @emailSubject + @sku
@importance = 'High',
@recipients= @email ,
@subject = @emailSubject
FETCH NEXT FROM c1 INTO @email,@sku