Why does this not work? I get the following error when trying to save the stored procedure. "Incorrect syntax near ')'."
SQL2005 SP1 developing with VS 2005 SP1
UPDATE mytable
SET modifyDate = getutcdate()
WHERE rowID = 1
I have also tried several variations all of which don't work.
DECLARE @.modifyDate datetime
Set @.modifyDate = getutcdate()
SELECT @.modifyDate getutcdate()
However if I use the getdate() instead any of the above work. ?
Larry
For some unknown reason the getutcdate() must be enclosed in () or the procedure ignores the WHERE clause portion of the procedure.
UPDATE mytable
SET modifyDate = (getutcdate())
WHERE rowID = 1
In my example in the first post the getutcdate() is not enclosed in () and the WHERE clause is not part of the query at least that is what VS 2005 determines.
As for the syntax error, that happened to be unrelated altogether except as a coding error. This error started popping up during a code review/update because of the law forcing businesses to save all email and IM correspondence. We determined that henceforth we must standardize our timebase and the logical time is UTC. So all our stored procedures that use a "modifyDate" field are being updated to now use UTC times. The syntax error appeared in just a few procedures that have "Administrative" functionallity that can modify the modifyDate from external code. We are now removing that capability from our system. It just so happens that in a few isolated cases the declaration of the modifyDate appeared last in the parameters section of the procedure. Thus when the line was commented out there was a trailing "," on the previous line that caused the syntax error.
Nevertheless VS2005 improperly detects the endpoint of the procedure when the getutcdate() is not enclosed in () itself.
Larry
|||UPDATE myTable
SET modifyDate = getutcdate()
WHERE id = 1
This one looks fine and works on my machine.
And this sp works too.
CREATE PROCEDURE [dbo].[tDate_sp]
AS
UPDATE myTable
SET modifyDate = getutcdate()
WHERE id = 1|||
I think that the issues may be with Visual Studio 2005 SP1.
If I create the stored procedure as shown above, what actually happens is the editor imporperly places the blue border that deliniates the SQL statement is such a way that the WHERE clause in this case is excluded. However SQL 2005 does accept the entire statement.
So in appearance the border outline in Visural Studio would have the following enclosed inside the statement:
UPDATE myTable
SET modifyDate = getutcdate()
This would NOT be inside the statement:
WHERE id = 1
The net effect is that it does work it is just imporperly indicated in the editor.
Larry
