4

For some reason, the following gives me an error:

DECLARE @Param1 DATETIME2(3)=...; -- Assign some date/time
DECLARE @Param2 DATETIME;
DECLARE @Param3 DATETIME;

EXEC dbo.SomeStoredProc CONVERT(DATE, @Param1), @Param2 output, @Param3 output;

The output is a compilation failure:

Msg 156, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'CONVERT'.

Changing it to the following fixes the problem:

DECLARE @Param1 DATETIME2(3)=...; -- Assign some date/time
DECLARE @Param2 DATETIME;
DECLARE @Param3 DATETIME;
DECLARE @TempDate DATE=CONVERT(DATE,@Param1);

EXEC dbo.SomeStoredProc @TempDate, @Param2 output, @Param3 output;
Charles
  • 50,943
  • 13
  • 104
  • 142
Michael Goldshteyn
  • 71,784
  • 24
  • 131
  • 181
  • 1
    possible duplicate of [Cast integer and concatenate to varchar in TSQL](http://stackoverflow.com/questions/4936180/cast-integer-and-concatenate-to-varchar-in-tsql) – Martin Smith Nov 22 '11 at 17:27

1 Answers1

4

Stored procedure parameters can't contain expressions, they have to be constant values or @ variables. In your example, CONVERT() is an expression and is therefore not allowed.

The same is true for default parameter values.

Cᴏʀʏ
  • 105,112
  • 20
  • 162
  • 194
  • 2
    Not quite true. They accept `@@` system functions. Just not the ones that take parentheses. So you can pass `@@IDENTITY` but not `SCOPE_IDENTITY()`. All a bit annoying. – Martin Smith Nov 22 '11 at 17:27