Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
If the condition in the IF block is provided, operations between the BEGIN and END blocks are performed. If the condition in the IF block is not provided, the operations between the BEGIN and END blocks after the ELSE block are performed.
You can use it as follows. We’re running the same IF … ELSE Statement twice by changing the parameter value. Thus, we enter both the IF block and the ELSE block.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | DECLARE @testvariable int=1 IF(@testvariable=1) BEGIN Select 1 END ELSE BEGIN Select 2 END SET @testvariable=2 IF(@testvariable=1) BEGIN Select 1 END ELSE BEGIN Select 2 END |
For a more detailed example, we create a table as below and add a few records.
1 2 3 4 5 6 7 8 9 10 11 | CREATE TABLE [dbo].[People]( [Name] [varchar](50) NOT NULL, [Age] [smallint] NOT NULL ) ON [PRIMARY] INSERT INTO [dbo].[People] ([Name],[Age]) VALUES ('Nurullah',34), ('Faruk',27), ('Hasan',54), ('Omer',2), ('Suleyman',64), ('Kerem',-1) |
Then, with the help of the following script, we find the maximum age in the table and if the maximum age is greater than 50, we delete that record or records from the table. If the maximum age is not greater than 50, we print a message.
1 2 3 4 5 6 7 8 9 10 11 12 13 | SELECT * FROM [dbo].[People] DECLARE @MAX_AGE INT SELECT @MAX_AGE=MAX(age) FROM [dbo].[People] IF(@MAX_AGE>50) BEGIN DELETE FROM [dbo].[People] WHERE age=@MAX_AGE END ELSE BEGIN print 'The maximum age is not greater then the condition' END SELECT * FROM [dbo].[People] |
