Order of parameters matters for sp_trace_create

Because each parameter can be specified by name, The order of each parameter in a T-SQL stored procedure call usually does not matter. For example, the stored procedure sp_spaceused takes two parameters and accepts both orders.

Execution of this script:

EXECUTE msdb.dbo.sp_spaceused
  @updateusage = 'true',
  @objname = N'msdb.dbo.sysjobs';
GO

EXECUTE msdb.dbo.sp_spaceused
  @objname = N'msdb.dbo.sysjobs',
  @updateusage = 'true';
GO

Returns two identical result sets:

name    rows    reserved    data    index_size  unused
sysjobs 1           64 KB   8 KB    56 KB   0 KB

name    rows    reserved    data    index_size  unused
sysjobs 1           64 KB   8 KB    56 KB   0 KB

But the stored procedure sp_trace_create is different. It takes a minimum of three parameters, and the order of these parameters does matter.

The first batch of the following script calls sp_trace_create with parameters in documentation order; the second batch swaps the positions of parameters @tracefile and @options:

DECLARE @new_trace_id INT;

EXECUTE master.dbo.sp_trace_create
  @trace_id = @new_trace_id OUTPUT,
  @options = 0,
  @tracefile = N'C:tempTestTrace';

SELECT @new_trace_id AS [@new_trace_id];

EXECUTE master.dbo.sp_trace_setstatus
  @trace_id = @new_trace_id,
  @status = 2;
GO

DECLARE @new_trace_id INT;

EXECUTE master.dbo.sp_trace_create
  @trace_id = @new_trace_id OUTPUT,
  @tracefile = N'C:tempTestTrace',
  @options = 0;

EXECUTE master.dbo.sp_trace_setstatus
  @trace_id = @new_trace_id,
  @status = 2;
GO

The first batch creates a new trace, selects its id, and then closes the trace. One result set is returned:

@new_trace_id
2

The second batch fails with an error:

Msg 214, Level 16, State 3, Procedure sp_trace_create, Line 1
Procedure expects parameter ‘@tracefile’ of type ‘nvarchar(256)’.

Huh? I passed a value for @tracefile! Its value is an nvarchar literal!

Why does parameter order affect the output of stored procedure sp_trace_create?

Why does it fail with such a confusing error message?