October 31, 2012 at 3:48 am
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
October 31, 2012 at 4:38 am
You have a parenthesis to many in the line above the IF statement.
You can change that to ...
set @currentdatetime = GETDATE()
October 31, 2012 at 4:42 am
Thank you, can't believe I missed that. Worked perfectly
October 31, 2012 at 9:20 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply