Casting string Variable

[robert030975]

I'm struggling to call the @Desp_Store in recipients and query. I need to use this variable to send out multiple stores.
Ive tried casting the string but seem to be missing something.

Using my name alone it in the recipient it generates 3 emails


code
for the #tmp use 'B001', B002', 'B003'

DECLARE @i int
DECLARE @Desp_Store varchar (4)
DECLARE @Desp_Store_table TABLE (
idx smallint Primary Key IDENTITY(1,1)
, Desp_Store varchar(4)
)
DECLARE @numrows int
-- populate store table
INSERT @Desp_Store_table
SELECT distinct Desp_Store FROM #tmp

-- enumerate the table
SET @i = 1
SET @numrows = (SELECT COUNT(*) FROM @Desp_Store_table)

IF @numrows > 0
WHILE (@i <= @numrows)
BEGIN

-- call the next store primary key
SET @Desp_Store = (SELECT Desp_Store FROM @Desp_Store_table WHERE idx = @i)

--
-- sending info out !

IF EXISTS ( SELECT 'Warehouse Despatch Advice for Store ' + @Desp_Store as Subject
FROM @Desp_Store_table Where idx = @i)
BEGIN
exec msdb.dbo.sp_send_dbmail @profile_name = 'LiveSMTPprofile',
@recipients = CAST('Robert.Docherty@blah;'AS NVARCHAR(50))+ CAST (''+@Desp_Store+''AS NVARCHAR(50))+ CAST('@blah' AS NVARCHAR(50)),
@query = 'SELECT Desp_Store, consignment_no from daily_load where Desp_Store = ''B001''',
@subject = 'TEST',
@body = 'THIS IS A TEST',
@attach_query_result_as_file = 0
end




-- increment counter
SET @i = @i + 1
END

 

[genomon]

What exactly is your question?

Ever notice how fast Windows runs? Me neither.