Search code examples
sql-serverdatestored-procedurestimestampgetdate

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


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()

Solution

  • You can use an intermediate variable:

        DECLARE @dt datetime =GETDATE()
    
        EXEC dbo.InsertTutees
           @StudentID = 2, 
           @FirstName = 'Sarah' , 
           @Surname = 'Smith' , 
           @Major = 'Science' ,
           @Timestamp = @dt