CODEWITHSUNDEEP
sqlsql-server-2008
chama
chama

Reputation: 6163

GETDATE() causing syntax error in SQL Server 2008

I just created a stored procedure to insert data into a table after performing some calculations. My create procedure code is below:

ALTER PROCEDURE [dbo].[myStoredProc] 
    @log_id INT,
    @job_nm VARCHAR(20),
    @feed_in_out_ind CHAR(1) = null,
    @process_dt DATETIME = null,
    @procedure_dt DATETIME = NULL,  
    @procedure_nm VARCHAR(20),
    @object_ty VARCHAR(20),
    @operation_ty VARCHAR(20),
    @num_records INT,
    @success_status BIT,
    @error_msg VARCHAR(50) = NULL,
    @start_time DATETIME,
    @end_time DATETIME = null
AS

When I try to call the stored proc, if I use the GETDATE() for any of the datetimes, I get a syntax error Incorrect syntax near ')' When I replace the GETDATE() with an actual datetime, the procedure runs correctly.

Here is my calling code:

EXEC myStoredProc 
    @log_id = 1, 
    @job_nm = 'It',
    @feed_in_out_ind = 'i',
    @process_dt = GETDATE(),
    @procedure_dt = GETDATE(),
    @procedure_nm = 'Test 1', 
    @object_ty = 'test',
    @operation_ty = 'test',  
    @num_records = 50, 
    @success_status = 0, 
    @error_msg = 'Hello',
    @start_time = GETDATE(),
    @end_time = GETDATE()

Any ideas? Thanks.

Upvotes: 2

Views: 11380

Answers (4)

Carson
Carson

Reputation: 516

Try

DECLARE @Now AS DATETIME
SET @Now = GETDATE()

EXEC myStoredProc 
    @log_id = 1, 
    @job_nm = 'It',
    @feed_in_out_ind = 'i',
    @process_dt = @Now ,
    @procedure_dt = @Now ,
    @procedure_nm = 'Test 1', 
    @object_ty = 'test',
    @operation_ty = 'test',  
    @num_records = 50, 
    @success_status = 0, 
    @error_msg = 'Hello',
    @start_time = @Now ,
    @end_time = @Now

Upvotes: 4

gbn
gbn

Reputation: 432180

Stored proc parameters take constants or NULL as defaults

From CREATE PROCEDURE

default

Is a default value for the parameter. If a default value is defined, the procedure can be executed without specifying a value for that parameter. The default must be a constant or it can be NULL.

Normally I'd do SET @dtparam = ISNULL(@dtparam, GETDATE()) in the stored proc

Upvotes: 2

JNK
JNK

Reputation: 65147

You can't call a function in the parameters AFAIK.

Something I have used is:

ALTER PROCEDURE [dbo].[myStoredProc] 
     @process_dt DATETIME = null

AS   

IF @process_dt IS NULL
    BEGIN       
        SET @TimeComplete = GETDATE()
    END

Add one of those for each of the variables you want to default to now. Or, declare a variable at the beginning of the proc (INSIDE IT), and set all the times to that. That way they are internally consistent (no millisecond variation between).

Upvotes: 3

Security Hound
Security Hound

Reputation: 2551

I suspect the reason you are having problems is you are attempting to call a function within the parameter of a stored procedure.

It has been over a year since I researched how stored procedures worked exactly if my memory is correct you should be able to store the current time into a local variable and use that value.

I guess I was to slow to post because the "workaround" was exactly what I suggested :$

Upvotes: 0

Related Questions