0

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='test@gmail.com',
     @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.

DineshDB
  • 5,998
  • 7
  • 33
  • 49

2 Answers2

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
sacse
  • 3,634
  • 2
  • 15
  • 24
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')
PeterHe
  • 2,766
  • 1
  • 8
  • 7
  • 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. – DineshDB Mar 17 '20 at 13:59