CODEWITHSUNDEEP
sql-servert-sql
SpartaSixZero
SpartaSixZero

Reputation: 2421

T-SQL syntax error when executing a stored procedure passing in a datetime variable as a varchar

My sql code is below:

 EXEC spMyStoredProcedure
   @StartDateTime='' + CONVERT(varchar(25), @today_date,121) + '',
   @EndDateTime=@today_date,
   @MaxDateTime='9999-12-31 23:59:59.997';

However, when I execute this in MSSQLServer Management Studio 2012, I see the following error in the message section:

Incorrect syntax near '+'.

Any suggestions?

Upvotes: 0

Views: 226

Answers (2)

Andrey Korneyev
Andrey Korneyev

Reputation: 26846

Define separate variable, set its value according to your needs and pass it as argument of stored procedure:

declare @date varchar(100)
select @date = '' + CONVERT(varchar(25), @today_date,121) + ''

EXEC spMyStoredProcedure
 @StartDateTime=@date,
 @EndDateTime=@today_date,
 @MaxDateTime='9999-12-31 23:59:59.997';

This is required because when you're calling stored procedure, all of this procedure parameters should be one of: constant values, variable name or DEFAULT keyword, but not expressions.

See EXEC MSDN article for details.

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93694

Try this. That's not a valid syntax

DECLARE @st VARCHAR(20) = '' + CONVERT(VARCHAR(25), @today_date, 121) + ''

EXEC Spmystoredprocedure
  @StartDateTime=@st,
  @EndDateTime=@today_date,
  @MaxDateTime='9999-12-31 23:59:59.997';

Upvotes: 0

Related Questions