Sql-server – Incorrect syntax near ‘)’ calling stored procedure with GETDATE

getdatesql servertsql

Maybe I am having a moment of 'afternoon', but can anyone explain why I get

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

When running

CREATE PROC DisplayDate 
    (@DateVar DATETIME) 
AS 
BEGIN
    SELECT @DateVar
END
GO

EXEC DisplayDate GETDATE();

Best Answer

You can't pass in a function call as an argument to your stored procedure. Instead use an intermediate variable:

DECLARE @tmp DATETIME
SET @tmp = GETDATE()

EXEC DisplayDate @tmp;

Related Solutions

Sql – Insert results of a stored procedure into a temporary table

You can use OPENROWSET for this. Have a look. I've also included the sp_configure code to enable Ad Hoc Distributed Queries, in case it isn't already enabled.

CREATE PROC getBusinessLineHistory
AS
BEGIN
    SELECT * FROM sys.databases
END
GO

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC getBusinessLineHistory')

SELECT * FROM #MyTempTable

Sql – Function vs. Stored Procedure in SQL Server

Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e., no INSERT or UPDATE statements allowed).

A function can be used inline in SQL statements if it returns a scalar value or can be joined upon if it returns a result set.

A point worth noting from comments, which summarize the answer. Thanks to @Sean K Anderson:

Functions follow the computer-science definition in that they MUST return a value and cannot alter the data they receive as parameters (the arguments). Functions are not allowed to change anything, must have at least one parameter, and they must return a value. Stored procs do not have to have a parameter, can change database objects, and do not have to return a value.

Best Answer

Related Solutions

Sql – Insert results of a stored procedure into a temporary table

Sql – Function vs. Stored Procedure in SQL Server

Related Topic