CODEWITHSUNDEEP
sqlsql-servert-sql
Sperick
Sperick

Reputation: 2761

Converting Varchar to Datetime for Stored procedure input

I want to use a datetime parameter in a stored procedure, in T-SQL, that if NULL will revert to 12/31/9999.

My code looks like this:

EXEC abc.someStoredProc @i_param1, @i_param2, ISNULL(@l_Termination_date, '12/31/9999')

I get an error:

Incorrect syntax near '@l_Termination_date'

I've tried using convert and cast (for example: 

 ISNULL(@l_Termination_date,CAST('12/31/9999' AS datetime))

but can't seem to get it right. What am I doing wrong?

Upvotes: 0

Views: 1553

Answers (2)

mohan111
mohan111

Reputation: 8865

DECLARE      @l_Termination_date DATE ; 
SET @l_Termination_date = ISNULL(@l_Termination_date,'12/31/9999')
EXEC   dbo.USP_abc.someStoredProc (
@i_param1
, @i_param2
,@l_Termination_date )

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239814

You can pass variables or literals as arguments to execute a stored procedure, (or call one of a few specific functions), but what you can't do is have arbitrary expressions.

Move it to a separate step:

SET @l_Termination_date = ISNULL(@l_Termination_date,'99991231')
EXEC        abc.someStoredProc
        @i_param1
      , @i_param2
        ,@l_Termination_date

(Or use a separate variable if you don't want to overwrite @l_Termination_date)

Upvotes: 4

Related Questions