SQL Server 2008 R2 If/ else syntax
10,214
You are missing an END in the end. Always indent your code. Not only it would look better, you would not run into these situations. Also, it is better to use BEGIN and END even if you have single statement in IF/ELSE or for that matter in any other block where a BEING/END is not required. It reduces maintenance nightmare when someone later can add a statement and break the code thinking that was part of the IF or ELSE block.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sprocAddSymbols] @symbol NVARCHAR(10)
AS
BEGIN
DECLARE @symbolCheck NVARCHAR(10)
DECLARE @statusCheck NVARCHAR(10)
SET @symbolCheck = (SELECT Symbol FROM tblSymbolsMain WHERE Symbol = @symbol)
SET @statusCheck = (SELECT SymbolStatus FROM tblSymbolsMain WHERE Symbol = @symbol)
IF (@symbolCheck IS NOT NULL AND @statusCheck = 'Inactive')
BEGIN
UPDATE tblSymbolsMain
SET SymbolStatus = 'Active'
WHERE Symbol = @symbol
END
ELSE
BEGIN
INSERT INTO tblSymbolsMain (Symbol, DateAdded, Status)
VALUES (@symbol, GETDATE(), 'Active')
END
END
Author by
StatsViaCsh
Updated on June 24, 2022Comments
-
StatsViaCsh about 2 years
I'm trying to write a fairly simple stored procedure in sql server. If the source of my error is obvious, I'm not catching it. SSMS is giving me "Incorrect syntax near ')'." Any help would be appreciated. Here's the procedure:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sprocAddSymbols] @symbol NVARCHAR(10) AS BEGIN DECLARE @symbolCheck NVARCHAR(10) DECLARE @statusCheck NVARCHAR(10) SET @symbolCheck = (SELECT Symbol FROM tblSymbolsMain WHERE Symbol = @symbol) SET @statusCheck = (SELECT SymbolStatus FROM tblSymbolsMain WHERE Symbol = @symbol) IF (@symbolCheck IS NOT NULL AND @statusCheck = 'Inactive') BEGIN UPDATE tblSymbolsMain SET SymbolStatus = 'Active' WHERE Symbol = @symbol END ELSE INSERT INTO tblSymbolsMain (Symbol, DateAdded, Status) VALUES (@symbol, GETDATE(), 'Active')