02 - Transact SQL - Begin-End

Hi,

 

Here we'll cover below –

 

-          Transact-SQL, an extension to the SQL database programming language, is a powerful language offering many features.

-          Transact-SQL provides the SQL Server developer with several useful functions, conditional processing methods, advanced transaction control, exception and error handling, scrollable cursors, and much more.

 

 

Here we'll cover below –

 

-          Use CASE

-          BEGIN and END

-          IF…ELSE

-          WHILE, BREAK, and CONTINUE

-          RETURN

-          RAISERROR

 

 

Use CASE, IF & WHILE, BREAK, CONTINUE

 

-          The Transact-SQL CASE expression allows a value to be returned conditionally.

-          The value returned is based on the value of the specified column or other expression.

 

 

 

CASE <optional_input_value> WHEN <expression_1> THEN <result_1> WHEN <expression_N> THEN <result_N> ELSE <end_result> END	SELECT vchCountry, CASE WHEN vchCountry = 'USA' THEN 'Western' WHEN vchCountry = 'Japan' THEN 'Eastern' ELSE 'I will tell you later...' END FROM Leaders
IF <expression_1> <Single_or_Batch_of_statements_if_TRUE> <expression_N> <Single_or_Batch_of_statements_if_TRUE> ELSE <Single_or_Batch_of_statements_if_TRUE>	IF (SELECT CURRENT_USER)= 'dbo' BEGIN PRINT 'I am dbo!' END ELSE BEGIN PRINT 'I am not dbo. ' END
WHILE <expression_being_evaluated_TRUE_or_FALSE> <SQL_statement_block>	WHILE @CustomerID IS NOT NULL BEGIN PRINT @CustomerID IF (@CustomerID)= 'CACTU' BEGIN   CONTINUE END SELECT @CustomerID = MIN(CustomerId) FROM Customers WHERE CustomerID > @CustomerID END

 

 

RETURN

 

-          Two uses –

-          Exit - The RETURN keyword exits the query or procedure immediately after being called. As with BREAK, no further processing is performed in the procedure or batch.

-          Return values - The RETURN statement can be used to return integer values.

 

 

RETURN <optional_integer_value>
PRINT 'This is before the batch.' SELECT TOP 1 * FROM INFORMATION_SCHEMA.Tables RETURN PRINT 'This statement never prints.   Output - 'This is before the batch   Here, The last PRINT statement does not run.	CREATE PROCEDURE ValidatePermissions AS IF (CURRENT_USER) <> 'dbo' BEGIN   RETURN 1 END ELSE RETURN 0   Output –   DECLARE @ReturnCode int EXEC @ReturnCode = ValidatePermissions PRINT @ReturnCode

 

 

 

RAISERROR

 

-          The RAISERROR command allows you to return either a user-defined error message (from the sysmessages table), or

-          An error message produced from a string.

 

 

RAISERROR (<message_id_OR_message_string>, <severity_level>,<message_state>, <arguments_N> ) WITH <LOG_or_NOWAIT_or_SETERROR>	First, a new error will be added to sysmessages: sp_addmessage 60002, 16, 'Warning, you have gone too far this time!'   This example then raises our new error: RAISERROR (60002, 16, 1) -------------------------------------------------------   EXEC sp_addmessage @msgnum = 60012, @severity = 16, @msgtext = N'You cannot make this change if you are %s.'   RAISERROR (60012, 16, 1, 'JaneDoe')
<severity_level>	The severity_level parameter allows levels 0 through 18 for regular users, and 19 through 25 for sysadmin members
<message_state>	Allows an integer value, between 1 and 127, to indicate a messages invocation state.
<arguments_N>	Allows one or more arguments that are used to formulate the message string.
WITH <LOG_or_NOWAIT_or_SETERROR>	-          The WITH LOG option is required for levels 19 through 25. -          The WITH LOG option logs the error to the application log. -          The WITH NOWAIT option sends the messages to the client, and WITH SETERROR sets @@ERROR's value to either the  message_id value or 50,000. -          The WITH LOG option can only be executed by members of the sysadmin role

 

 

 

Thanks & Regards,

Arun Manglick || Tech Lead