Get values dynamically from tables while sending mails from SQL Server

Question

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.

sacse · Accepted Answer · 2020-03-17 14:36:21Z

2

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

answered Mar 17, 2020 at 14:36

sacse

3,8272 gold badges19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DineshDB Over a year ago

Thanks for the suggestion. I have made the change in the table design.

PeterHe · Accepted Answer · 2020-03-17 13:53:29Z

1

Put the function outside of the string:

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

answered Mar 17, 2020 at 13:53

PeterHe

2,7861 gold badge11 silver badges7 bronze badges

1 Comment

DineshDB Over a year ago

Thanks for your answer Peter. But when you insert like this, you will always get the inserted time only. But I want the execution time, not the insertion time.

Collectives™ on Stack Overflow

Get values dynamically from tables while sending mails from SQL Server

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related