Applying Batches, Stored Procedures and Functions Part1

Introduction

Begin a database designer; an individual may want to implement a group of SQL commands completely. The SQL Server permits an individual to form bunches by means of several commands which can be implemented entirely and this bunches are termed as Batches. These batches can be comprised of execution – controlling commands which contain provisional sensibleness to inspect the situations in advance of implementing the commands.

From time to time, it may be mandatory to implement a batch over and over again, but not at the same period. In this type of situations, a batch can be kept as database items termed as Stored Procedures as well as Functions. These database items enclose a pre – compiled batch which can be implemented numerous times devoid of re – compilation.

This article will be clarifying in what way to form batches to implement several SQL commands. Supplementary, it clarifies in what manner to implement the stored procedures as well as functions in SQL Server. From this article an individual will acquire knowledge to apply batches, stored procedures in addition to functions.

Applying Batches

Begin a database designer; an individual may want to implement numerous SQL commands to accomplish a single job. For an instance, whenever a new customer opens a fresh bank account irrespective of the account type in a bank, the database designer must add the customer particulars in the database. The particulars of the customer are kept in more than one table or relation. Consequently, the individual must implement an add command to stock the particulars in every single table or relation. For this type of situations, the individual can direct every SQL commands in total to the SQL Server for getting implemented as a single item, which in consequence, support in dropping the network circulation too.

From time to time, an individual may want to examine the settings prior to implementing the SQL commands. For an instance, in a loan approval division, when an individual add a record in the LoanPending table or relation to save the particulars of a loan that is pending for approval by the loan approval division, that individual must examine first that there is no pending loan for a particular customer in past who have applied for the loan currently. For this situation, an individual must form conditional hypotheses which will scrutinize intended for a condition in advance of implementing any commands.

Constructing Batches

A batch is a collection of SQL commands that has been given altogether to the SQL Server for implementation. When implementing batches, the SQL Server collects the commands of a batch into a distinct runnable item known as an Execution Plan. This saves the performance time.

Think through an instance, an individual have to run 5 (five) commands plus the individual is implementing them one at a time by way of directing 5 (five) distinct appeals. This procedure consumes time when unknowingly the queries get into a queue. The whole thing that is the 5 (five) commands may not get implemented as a single item. As an alternative, if an individual implement every 5 (five) commands altogether in a batch, at that moment the implementation procedure turn out to be more rapid as every commands are directed to the server in total. In the direction of constructing a batch, an individual can write several SQL commands trailed by the keyword GO at the completion, for an instance refers the subsequent item:-

< T – SQL Command 1 >

< T – SQL Command 2 >

< T – SQL Command 3 >

< T – SQL Command 4 >

< T – SQL Command 5 >

GO

GO is a keyword which stipulates the conclusion of the batch as well as directs the SQL commands to the SQL Server.

Think through an instance. If an individual need to stock the particulars of new loan request in the Banking database, then the individual can form the subsequent batch:-

INSERT INTO [ XYZBank ] . [ Customer ] . [ Details ] ( CustomerID , Name , Gender , Address , EmailID , Phone , SocialSecurityID , AccountType ) VALUES ( ‘ C#25001 ’ , ‘ My Name ’ , ‘ Male ’ , ‘ My Address Line 1 , Address Line 2 ’ , ‘ MyEmailID . com ’ , ‘ 0123456789 ’ , ‘ SSID#3535359 ’ , ‘ Loan ’ )

INSERT INTO [ XYZBank ] . [ Customer ] . [ Loan ] ( CustomerID , LoanID , LoanType , ROI , LoanAmt , EMI , TimePeriod ) VALUES ( ‘ C#25001 ’ , ‘ L#003 ’ , ‘ Personal ’ , 12 . 43 , ‘ 20000 ’ , ‘ 4000 ’ , ‘ 2 Years ’ )

GO

Whenever a batch is given to the SQL Server, it is assembled to form an execution plan. If some compiling fault happens, for an instance coding mistake, the execution plan is not formed. Hence, not a single command in the batch is implemented. But, once the execution plan is ready and uncertainly an execution – time fault happens, the implementation of the batch discontinues. For this type of scenario, the commands that are implemented earlier to the command which came across the execution – time mistake are unaffected.

Batch By Means Of Variables

When forming batches, an individual may want to store a number of data provisionally throughout the implementation time. For an instance, an individual may want to save a number of in – between data of the calculations. In the direction of saving the transitional data, an individual can use variables as well as allocate data to them. An individual can use a variable by means of casting off the DECLARE command. The code of the DECLARE command is:-

DECLARE @ My_Variable_Name DataType

The variables which are declared in a batch as well as can be castoff in every command within the batch are known as Local Variables. The subsequent syntax declares a variable named, @ ROI plus allocates the average value of the ROI column or attribute form the Customer . Loan table or relation to the variable

DECLARE @ ROI FLOAT

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

GO

In the previous instance, the @ ROI variable is declared plus castoff to save the average value of the ROI column or attribute. The AVG summative system function has been castoff to recover the average rate of interest from the Customer . Loan table or relation. The GO keyword is castoff to direct every command as a single item to the SQL Server.

In the upcoming article we will be discussing How To Show User Demarcated Message?, Guiding Principle For Constructing Batches, Conditional Hypotheses under which the three (3) types of conditional hypotheses which are Conditional Block through IF – ELSE Command, Conditional Block through CASE Command, and Conditional Block through WHILE Command will also be discussed.