How to use convert() function?

Question

I'd like to convert a datetime to nvarchar(64). The following code is a snippet used as part of a series of stored procedure parameters. I've included the area that has the error.

Incorrect syntax near KEYWORD 'CONVERT'

exec mysproc...@Password=N'',@IsAuthorized=1,
@UTCTIMESTAMP=CONVERT(NVARCHAR(64), GETUTCDATE(), 121),
@UserId=@SingleId output

Any ideas what is wrong with this syntax?

You need to use an intermediate variable as you can't use most functions inline in a stored procedure parameter list (exceptions are the `@@` system functions such as `@@SPID` that used to be called global variables) — Martin Smith, Jan 18 '13 at 22:34

score 6 · Accepted Answer · answered Jan 18 '13 at 22:48

You need to perform calculations like this prior to calling the stored procedure, you can't do that within the process of passing values to parameters. For example:

DECLARE @utc NVARCHAR(64) = CONVERT(NVARCHAR(64, GETUTCDATE(), 121);

EXEC mysproc 
  @Password = N'',
  @IsAuthorized = 1,
  @UTCTIMESTAMP = @utc,
  @UserId = @SingleId OUTPUT;

This isn't restricted to function calls; you also can't do things like:

EXEC sp_help @objname = N'sys.' + 'tables';

Result:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '+'.

How to use convert() function?

1 Answers1