Incorrect syntax near the keyword 'IF'.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Incorrect syntax near the keyword 'IF'.

Incorrect syntax near the keyword 'IF'.

Rate Topic

Display Mode

Topic Options

Author

Message

matthew.shaw

Posted Wednesday, October 31, 2012 3:48 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, November 05, 2012 1:18 AM
Points: 2, Visits: 3

I'm trying to create a stored procedure, but I keep getting "Incorrect syntax near the keyword 'IF'."

Below is my full script.

CREATE PROCEDURE [dbo].[uspCreateCaseApp]
@caseid int,
@userid int
AS

declare @commid int
declare @commlinkid int
declare @currentdatetime datetime
declare @territoryid int

set @currentdatetime = (SELECT (GETDATE())

IF @caseid IS NOT NULL AND @userid IS NOT NULL

BEGIN
select @territoryid=oppo_secterr from cases with(nolock) where case_caseid=@caseid

exec @commid = dbo.eware_get_identity_id 'Communication' --Another stored procedure
that returns a number, this is working fine
INSERT INTO [Communication]
([Comm_CommunicationId]
,[Comm_Type]
,[Comm_Action]
,[Comm_Status]
,[Comm_DateTime]
,[Comm_ToDateTime]
,[Comm_CreatedBy]
,[Comm_CreatedDate]
,[Comm_UpdatedBy]
,[Comm_UpdatedDate]
,[Comm_TimeStamp]
,[Comm_SecTerr]
,[Comm_CaseId]
,[Comm_Organizer]
,[comm_note])
VALUES
(@commid
,'Appointment'
,'Meeting'
,'Pending'
,@currentdatetime
,@currentdatetime
,1
,@currentdatetime
,1
,@currentdatetime
,@currentdatetime
,@territoryid
,@Caseid
,@userid
,'This is a Test Case Communication')

exec @commlinkid = dbo.eware_get_identity_id 'Comm_Link'
INSERT INTO [Comm_Link]
([CmLi_CommLinkId]
,[CmLi_Comm_CommunicationId]
,[CmLi_CreatedBy]
,[CmLi_CreatedDate]
,[CmLi_UpdatedBy]
,[CmLi_UpdatedDate]
,[CmLi_TimeStamp]
,[CmLi_Comm_UserId])
VALUES
(@commlinkid
,@commid
,1
,@currentdatetime
,1
,@currentdatetime
,@currentdatetime
,@userid)
end
GO

Post #1379216

Mikael Eriksson SE

Posted Wednesday, October 31, 2012 4:38 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 16, 2013 5:05 AM
Points: 157, Visits: 310

You have a parenthesis to many in the line above the IF statement.

You can change that to ...

set @currentdatetime = GETDATE()

Post #1379236

matthew.shaw

Posted Wednesday, October 31, 2012 4:42 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, November 05, 2012 1:18 AM
Points: 2, Visits: 3

Thank you, can't believe I missed that. Worked perfectly

Post #1379238

Sean Lange

Posted Wednesday, October 31, 2012 9:20 AM

SSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 8:46 AM
Points: 8,547, Visits: 8,204

I know you fixed your syntax issue but I can't help but ask about your stored proc.

"exec @commid = dbo.eware_get_identity_id 'Communication' --Another stored procedure

I assume this is getting the next "ID" for the given table. Are you handling concurrency with this? I only ask because I have seen this type of thing before and the inside of that is something like

exec ('select Max(' + @TableName + 'ID) from ' + @TableName)

This type of thing can be a total pain in the backside to straighten out when concurrency becomes an issue as the number of users ramps up. If you are handling concurrency then the question, why not just use an identity instead?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1379382

« Prev Topic | Next Topic »

Permissions