0

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.*

stored procedure wtfman

  • Why are you casting a date as nvarchar then casting back to date? Also stored procedure parameters can't take expressions - only constants and variables. Finally, don't use nvarchar without length, and never mind why is this a string, why is it nvarchar instead of varchar? – Aaron Bertrand Jan 29 '15 at 19:05
  • 1. In sprocs called before this one I am using datetime, in this one I only want the date. 2. I'm not, the first is a select, not a set 3. OK, that explains it. 4. Will do. 5. Because the sproc uses dynamic SQL, as stated in the question. – n8. Jan 29 '15 at 19:08
  • Dynamic SQL (or date only) are terrible excuses for converting a date to a string. Your dynamic SQL should still be safely parameterizing a date variable through sp_executesql, not concatenating and double-single-quoting some date that has been changed to a string. – Aaron Bertrand Jan 29 '15 at 19:10
  • I didn't know that, thanks for your answer. – n8. Jan 29 '15 at 19:11
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.