Applying Batches, Stored Procedures and Functions Part 2

How To Show User Demarcated Message?

Every now and then, an individual may want to show message and or data the variables once the batch has been implemented successfully. On this occasion, an individual can practice the PRINT command, as presented in the subsequent batch. The subsequent syntax shows the data of the ROI variable by means of the PRINT command:-

DECLARE @ ROI FLOAT

SELECT @ ROI = AVG ( ROI ) FROM Customer . Loan

PRINT @ ROI

GO

The individual can practice keeping the remark records in batches to mark a explanation of the syntax. This will benefit in understanding the resolution of the syntax. A remark record can be inscribed in 2 (two) methods:

· Several line remark records surrounded inside /* and */

· Single line remark record beginning with – – (dual dashes)

Guiding Principle For Constructing Batches

At the time of forming batches, an individual must follow the subsequent recommendations:-

· Association of the commands is not possible, for an instance CREATE TRIGGER, CREATE DEFAULT, CREATE VIEW, CREATE FUNCTION, CREATE RULE, and CREATE PROCEDURE are not possible to be combined with another command when forming batch. Every command which trails the CREATE command is understood as a portion of the demarcation.

· The EXECUTE command can be used in the batch if it isn’t the leading command of the batch, or else the EXECUTE command functions indirectly.

Furthermore, an individual must follow the subsequent limitations:-

· Binding rules as well as defaults to columns or attributes plus using them in the similar batch is not possible.

· Check constriction cannot be demarcated as well as castoff in the similar batch.

· Deleting of an item as well as recreating the identical object in the similar batch is not possible.

· Modifying a table or relation by means of inserting a new column or attribute as well as then mention the new column or attribute in the batch formed before is not possible.

Conditional Hypotheses

The SQL Server permits an individual to practice program writing concepts in the batches intended for provisional implementation of commands. For an instance, an individual want to recover information grounded on a situation. If the situation is not fulfilled, an error message must be exhibited. The SQL Server lets an individual to practice the subsequent conditional blocks to governor the course of commands:-

· IF – ELSE command

· CASE command

· WHILE command

Conditional Block through IF – ELSE Command

An individual can practice the IF – ELSE command for restrictive implementation of SQL commands. A specific act is accomplished as soon as the specified situation value is equal to be TRUE as well as an additional act is accomplished as soon as the specified situation value is equal to be FALSE. The code of the IF – ELSE command is:-

IF My_Boolean_Expression

{ My_SQL_Command | My_Command_Block }

ELSE

{ My_SQL_Command | My_Command_Block }

here,

· My_Boolean_Expression – It stipulates the situations which are to be measured to get either TRUE or FALSE.

· My_SQL_Command – It stipulates the T – SQL command.

· My_Command_Block – It is a group of T – SQL commands.

Think through an instance, where a scrutiny is made to realize whether the Education loan facility is present or not. If the Education loan facility is present, then every particulars of the loan is presented or else, a custom message is shown.

IF EXISTS ( SELECT * FROM [ Management ] . [ LoanDetails ] WHERE Type = ‘ Education ’ )

BEGIN

SELECT * FROM [ Management ] . [ LoanDetails ] WHERE Type = ‘ Education ’

END

ELSE

PRINT ‘ Education Loan Not Present. ’

GO

Conditional Block through CASE Command

An individual can practice the CASE command in circumstances where more than a few situations are to be valued. The CASE command measures a list of situations plus yields back only one of the probable results. An individual can practice the IF command to do the identical job. But, an individual can practice a CASE command at times when there are more than two situations that need to evaluate a shared variable for dissimilar data. The code of the CASE command is:-

CASE

WHEN My_Boolean_Expression THEN My_Action_Expression

[ WHEN My_Boolena_Expression THEN My_Action_Expression ]

[ ELSE My_Action_Expression ]

END

here,

· My_Boolean_Expression – It stipulates the situations which are to be measured by means of the CASE block.

· My_Action_Expression – It is the consequential expression part which is implemented as soon as the My_Boolean_Expression results to TRUE. The expression can be a column or attribute name, a constant, a query, a function or else some amalgamation of arithmetic, string as well as bit – wise operators.

For an artless CASE block, either a variable or an expression is matched by the My_Boolean_Expression in every single WHEN clause. If a single expressions results to be TRUE, at that moment the expression stated with the THEN clause is implemented which is the My_Action_Expression, otherwise if none of the expressions results to be TRUE, the expression in the ELSE block is implemented.

Think through the subsequent instance where a case block is involved in the SELECT command to show the gender of the customer as ‘ Male ’ or ‘ Female ’:-

SELECT CustomerID , Name , ‘ Gender ’ =

CASE Gender

WHEN ‘ M ’ THEN ‘ Male ’

WHEN ‘ F ’ THEN ‘ Female ’

ELSE ‘ Not Specified ’

END

From [ XYZBank ] . [ Customer ] . [ Details ]

GO

Conditional Block through WHILE Command

An individual can practice the WHILE command in a batch to permit a group of T – SQL commands to implement frequently on the assumption that the specified situation results to be true. The code of the WHILE command is:-

WHILE My_Boolean_Expression

{ My_SQL_Command | My_Command_Block }

[ BREAK ]

{ My_SQL_Command | My_Command_Block }

[ CONTINUE ]

here,

· My_Boolean_Expression – It stipulates the situations which are to be measured to get either TRUE or FALSE.

· My_SQL_Command – It stipulates the T – SQL command.

· My_Command_Block – It is a group of T – SQL commands.

· BREAK – It is the reason why the programming control leaves the WHILE loop.

· CONTINUE – It is the reason why the programming control resumes the WHILE loop, avoiding every commands that were mentioned subsequently after the CONTINUE keyword.

The SQL Server offers the BREAK as well as CONTINUE commands to regulate the commands inside the WHILE loop. The BREAK command is the reason why the programming control exits from the WHILE loop. Every command which seems to be subsequently after the END keyword, are implemented right afterwards the BREAK command is implemented and it marks the end of the loop as well. The CONTINUE command is the reason why the WHILE loop gets resumed, avoiding every commands afterwards the CONTINUE command within the loop.

Think through the subsequent instance where the management division of the XYZBank, has certainly thought to update the ROI for every type of loans which are having an interest rate below 10.50%. According to the present management strategy, the minimum ROI for every loan type must be just about 10.50% but at the same time Education loan will be of lower interest rate which means it will be below 10.50%. An individual wants to upsurge the ROI for every loan type which are having ROI below 10.50% and the loan type is not Education.

WHILE ( SELECT MIN ( ROI ) FROM [ Management ] . [ LoanDetails ] ) < 10 . 50

BEGIN

UPDATE [ Management ] . [ LoanDetails ] SET ROI = 10 . 50 WHERE ROI < 10 . 50 AND LoanType ! = ‘ Education ’

IF ( SELECT MIN ( ROI ) FROM [ Management ] . [ LoanDetails ] ) = 10 . 50

BREAK

ELSE

CONTINUE

END

In the upcoming article we will be discussing about Managing Inaccuracies Plus Bugs, with the help of two (2) types of managing possibilities which are Managing Errors with TRY – CATCH Block, Managing Errors with RAISERROR Command.