Get values dynamically from tables while sending mails from SQL Server

I have a table:

CREATE TABLE A(Id INT, Val VARCHAR(100))
INSERT INTO A VALUES (1,'MyData+format(getdate(),''yyMMdd'')+.CSV')

DECLARE @out VARCHAR(100)
SELECT @out = Val FROM A WHERE Id = 1

EXEC sp_send_dbmail @profile_name='MyProfile',
     @recipients='[email protected]',
     @subject=@out,
     @body='This is the body of the test message.'

When I execute the Query, I received the mail with subject like 'MyData+format(getdate(),'yyMMdd')+.CSV', but I want the output like 'MyData20200317.CSV'.

I don't want to merge the date field outside of the table, I definitely want the functionality should be in the table, and we should process the data and get the result.

Note:

If it is possible to change the value in the table means, that also fine. But the values should be pick from the table.

I hope it is doable. Thanks in advance.

Solution

You can do this by altering the table structure a bit.

try the following:

CREATE TABLE #A(Id INT, Val_START VARCHAR(100), Val VARCHAR(100), Val_END VARCHAR(100))
INSERT INTO #A VALUES (1,'MyData','format(getdate(),''yyMMdd'')', '.CSV')

DECLARE @SQL NVARCHAR(MAX)
DECLARE @OUT VARCHAR(100)
SET @SQL = N'SET @OUT = ' + (SELECT ''''+Val_START+'''+' + VAL + '+'''+Val_END+''''FROM #A WHERE Id = 1)

exec sp_executesql @sql, N'@OUT VARCHAR(100) output', @OUT out
SELECT @OUT