Applying Batches, Stored Procedures and Functions Part – 5

Applying Parameters In A Stored Procedure

Once in a while, an individual want to execute a stored procedure for dissimilar data of a variable which are given at executing time. So, an individual can from a stored procedure which includes the parameter inside it. Factors are castoff to deliver the data to the stored procedure at the time of execution. These data can be delivered by means of normal variables. The factor which delivers the data is demarcated as input parameter. A stored procedure has ability of practicing a maximum of 2100 parameters. Every single factor has a name, data type, course as well as a default value.

The subsequent instance builds a stored procedure showing the customer ID, the loan ID and the type of loan which have the identical loan type given as an input at the time of implementation.

CREATE PROCEDURE SP_CustomerLoan

@ Type CHAR ( 50 )

AS

BEGIN

PRINT ‘ Loan Details For The Loan Type – ’ + @ Type

SELECT CustomerID , LoanID , LoanType FROM [ Customer ] . [ Loan ] WHERE LoanType = @ Type

END

Implement the stored procedure, SP_CustomerLoan by means of the subsequent command:

EXECUTE SP_CustomerLoan ‘ Education ’

When implementing the stored procedures, an individual can offer the data for the parameters by means of explicitly stipulating the parameter name plus data of the parameter too. In the preceding instance an individual can give the parameter data by means of the parameter name, as presented in the subsequent SQL command:

EXECUTE SP_CustomerLoan @ Type = ‘ Education ’

Giving Back The Data From Stored Procedure

Alike to give the input data to the stored procedures at execution time, an individual can yield back data as output from the stored procedure too. The data can be reverted back to the invoking system over the output parameters. In the direction of stipulating a parameter as the output parameter, an individual can use the OUTPUT keyword.

The OUTPUT keyword has to be indicated in both the CREATE PROCEDURE as well as the EXECUTE command. If the OUTPUT keyword is absent in either of one then the stored procedure will be implemented nevertheless it will not yield back any data. The code of the using an output parameter by means of the OUTPUT keyword is:

CREATE PROCEDURE My_Procedure_Name

[ { @ My_Out_Parameter data type } [ OUTPUT ] ]

AS

My_SQL_Command

here,

· @ My_Out_Parameter data type OUTPUT – It permits the stored procedure to direct a data value to the invoking stored procedure. Uncertainty, if the OUTPUT keyword is absent, then the parameter is taken as an input parameter.

An individual can give back the data form the stored procedure by means of the RETURN keyword too. The RETURN command permits the stored procedure to give back merely an integer data to the invoking system. The code of the RETURN command is:

RETURN My_Value

here,

· My_Value – It is some integer data that gets returned back to the invoking stored procedure.

When a data is not stated, then the stored procedure gives back a default data of 0 to stipulate unsuccessful on the other hand it gives back 1 to stipulate success.

Think through an instance. An individual wants to show particulars of a customer whose customer ID has been given as an input. Intended for this, an individual must form a stored procedure SP_CustomerDetails which will take customer ID as input as well as will give back the name of the customer, loan ID and the type of loan that the customer has taken. An individual can create the stored procedure, as presented in the subsequent command:

CREATE PROCEDURE SP_CustomerDetails

@ CustomerID VARCHAR ( 20 ) , @ Name CHAR ( 30 ) OUTPUT , @ LoanID VARCHAR ( 10 ) OUTPUT , @ LoanType CHAR ( 25 ) OUTPUT

AS

BEGIN

IF EXISTS ( SELECT * FROM [ Customer ] . [ Details ] WHERE CustomerID = @ CustomerID )

BEGIN

SELECT @ Name = D . Name , @ LoanID = L . LoanID , @ LoanType = L . LoanType FROM Details D JOIN Loan L ON D . CustomerID = L . CustomerID WHERE CustomerID = @ CustomerID

RETURN 1

END

ELSE

RETURN 0

END

The previous stored procedure takes the customer ID as an input parameter plus gives back the customer name, loan ID and loan type as output parameters. The stored procedure first examines the presence of the specified customer ID. If it is present, the stored procedure gives back an integer value 1 along with the requisite particulars, otherwise it gives back 0 only.

Invoking A Stored Procedure From A Different Stored Procedure

On occasion, an individual may want to use the data given back by a stored procedure into a different stored procedure. Intended for this, an individual can execute or else invoke ones stored procedure from inside a different stored procedure. A stored procedure which invokes otherwise implements a different stored procedure is identified as the invoking stored procedure, as well as the stored procedure which is invoking otherwise implemented by means of the invoking stored procedure is called as the invoked stored procedure. An individual can execute a stored procedure from a different stored procedure if an individual want to practice the functionality given by one into an additional.

Think through the earlier instance where the SP_CustomerDetails stored procedure yields back the customer particulars for a specified customer ID. An individual can form the SP_CustomerLoanDetails stored procedure, that takes the customer ID of a customer as an input plus shows the customer name, loan ID and loan type that the customer has taken from the bank accompanied by the customer address and the EMI amount of the loan. In the direction of achieving this job, an individual wants to invoke the SP_CustomerDetails stored procedure from the SP_CustomerLoanDetails stored procedure, as presented in the subsequent command:

CREATE PROCEDURE SP_CustomerLoanDetails

@ CustomerID VARCHAR ( 20 )

AS

BEGIN

DECLARE @ Name CHAR ( 30 )

DECLARE @ LoanID VARCHAR ( 10 )

DECLARE @ LoanType CHAR ( 25 )

DECLARE @ ReturnValue INT

EXECUTE @ ReturnValue = SP_CustomerDetails @ CustomerID , @ Name OUTPUT , @ LoanID OUTPUT , @ LoanType OUTPUT

IF ( @ ReturnValue = 1 )

BEGIN

PRINT ‘ Customer ID – ’ + CONVERT ( CHAR ( 20 ) , @ CustomerID )

PRINT ‘Customer Name – ’ + @ Name

PRINT ‘Loan ID – ’ + CONVERT ( CHAR ( 20 ) , @ LoanID )

PRINT ‘Loan Type – ’ + @ LoanType

SELECT D . Address , L . EMI FROM Details D JOIN Loan L ON D . CustomerID = L . CustomerID WHERE CustomerID = @ CustomerID

End

Else

Print ‘ Customer Not Found. ’

End

In the direction of executing the above syntax, an individual can use the subsequent command:

EXECUTE SP_CustomerLoanDetails ‘ C#25001 ’

The SQL Server offers a function, @@ROWCOUNT, that gives back the total number of rows or tuples has been affected through the last command. An individual can practice this command in the IF block to examine the outcome of the last command which was implemented.

In the upcoming article we will be discussing Applying Functions, Constructing User – Defined Functions (UDFs) and two (2) types of function which are Forming Scalar Functions and Forming Table – Valued Functions.