Is it possible to insert current datetime in a stored procedure which is designed to insert data?

Question

I've created the following stored procedure:

CREATE PROCEDURE dbo.InsertTutees 
    @StudentID INT = NULL, 
    @FirstName VARCHAR(50) = NULL, 
    @Surname   VARCHAR(50) = NULL, 
    @Major     VARCHAR(50) = NULL,
    @Timestamp DATETIME = NULL  
AS 
BEGIN 
    SET NOCOUNT ON 

    INSERT INTO dbo.Tutees (StudentID, FirstName, Surname, Major, Timestamp) 
    VALUES (@StudentID, @FirstName, @Surname, @Major, @Timestamp) 
END 
GO

EXEC dbo.InsertTutees 
         @StudentID = 2, 
         @FirstName = 'Sarah', 
         @Surname = 'Smith', 
         @Major = 'Science',
         @Timestamp = '2013-12-12';

However I would like to replace the timestamp value with the current timestamp. I've tried GETDATE(), but I get the following error:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'

Is there an alternative way to retrieve the current date inside this exec statement? Should I edit my stored procedure?

Thanks for your help!

EDIT: This is the exec statement I try to complete:

EXEC dbo.InsertTutees
       @StudentID = 2, 
       @FirstName = 'Sarah' , 
       @Surname = 'Smith' , 
       @Major = 'Science' ,
       @Timestamp = getdate()

Please edit your question to show the code including `GETDATE()` that gave you the error message. Is it possible you were trying to pass in `GETDATE()` as a parameter to the stored procedure? — AlwaysLearning, Jun 10 '20 at 12:22
Added my attempted script which leads me to the above error. Thanks! — codingnoob, Jun 10 '20 at 12:28

score 4 · Accepted Answer · answered Jun 10 '20 at 12:34

4

You can use an intermediate variable:

    DECLARE @dt datetime =GETDATE()

    EXEC dbo.InsertTutees
       @StudentID = 2, 
       @FirstName = 'Sarah' , 
       @Surname = 'Smith' , 
       @Major = 'Science' ,
       @Timestamp = @dt

answered Jun 10 '20 at 12:34

Anganthier

126
3

Ah, misunderstood what you were asking for. Cool. Glad it worked. – Grant Fritchey Jun 10 '20 at 12:42

score 3 · Answer 2 · answered Jun 10 '20 at 12:41

If you look it up in the documentation, a default has to be a constant or NULL. In order to have a default value that equates to GETDATE(), you'd need some code within the proc along these lines:

IF @Timestamp IS NULL
BEGIN
SET @Timestamp = GETDATE();
END

That would allow you to both provide a value and supply a default as needed.

Is it possible to insert current datetime in a stored procedure which is designed to insert data?

2 Answers2