Can't cast a stored procedure parameter?

Question

I have several stored procedures that take a date/time input, one of which takes a date only in the form of a nvarchar because it utilizes dynamic SQL. When I try to cast the variable going into the stored procedure, I get an error. This doesn't make much sense to me.

I know I can work around it by creating a second set of nvarchar variables or casting within the stored procedure, but I'd like some insight on why I can't just cast the variable in-line.

Example. *TOP is a Select statement telling me my NVARCHAR DATE kicks out just fine.

MID is where SSMS is throwing the error, BOTTOM is where SSMS thinks it's just fine.*

Answer 1

Stored procedure parameters can only accept literals, constants or variables as parameters. It can't take expressions of any kind.

You can say:

EXEC dbo.myprocedure @foo = 'bar';

But you can't say:

EXEC dbo.myprocedure @foo = 'b' + 'ar';

You should not be converting a date to a string and then back to a date again. Your dynamic SQL can easily take a date parameter, parameterized safely (you may want to read up on "SQL injection"), and not have to worry about ugly string concatenation. Here is an example:

CREATE PROCEDURE dbo.foo
  @dt DATE
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @sql NVARCHAR(MAX);
  SET @sql = N'SELECT DATEPART(DAY, @dt);';
  EXEC sys.sp_executesql @sql, N'@dt DATE', @dt;
END
GO

DECLARE @fromDay DATETIME = '20010914 03:45';
DECLARE @dt DATE = @fromDay; -- or just declare @fromDay as DATE in the first place;
EXEC dbo.foo;

Results:

----
14