Design an algorithm to compare two numbers

Input : Two numbers that are to be compared
Output : Messages of comparison

COMPARE (N1, N2)
[N1 and N2 are the numbers]
If(N1==N2) Then :
Write : 'Both the numbers are equal'
Else:
If (N1<N2) Then:
Write : 'First number is smaller then second number'
Else:
Write : 'First number is bigger number than second number'
[End of If]
[End of If]
Exit

Design an algorithm to find the sum, difference and product of two numbers

Input : From the problem definition the input to the algorithm will be two numbers.
NOTE : The identification of the data type of the input is not necessary like int or float.
Output : The result sum, difference and product are to be displayed.

ARTH_OPNS(N1,N2)
[N1 AND N2 are the numbers]
RES <-- N1+N2
Write: "The Sum is'. RES
RES <-- N1-N2
Write: ' The Difference is', RES
RES <-- N1* N2
Write: 'The Product is', RES
Exit

Design an algorithm to search a number using linear search technique

Input : A list (array) of number, number of elements in the list and key to search.
Output : Returns 1 if the key is found otherwise returns 0.

LSEARCH(LIST, N, KEY)
[LIST is an array of numbers, N is the size of the array and KEY is the number to search]
Repeat For I=0,1,2,3.....N-1   [Assuming that array index start from 0]
If (KEY == LIST[I] ) Then:
Return 1
[End of If]
Return 0
Exit.

How to Implement User Defined Function in SQL

Similar to the stored procedures, you can also create functions to store a set of T-SQL statements permanently. These functions are also referred to as user-defined functions (UDFs). A UDF is a database object that contains a set of T-SQL statements, accepts parameters, performs an action, and returns the result of that action as a value. The return value can either be a single scalar value or a result set.

UDFs have a limited scope as compared to stored procedures. You can create functions in situations when you need to implement a programming logic that does not involve any permanent changes to the database objects outside the function. For example, you cannot modify a database table from a function.

UDFs are of different types: scalar functions and table-valued function. As a database developer, it is important for you to learn to create and manage different types of UDFs.

Creating UDFs

A UDF contains the following components:

• Function name with optional schema/owner name
• Input parameter name and data type
• Options applicable to the input parameter
• Return parameter data type and optional name
• Options applicable to the return parameter
• One or more T-SQL statements

To create a function, you can use th CREATE FUNCTION statement. The syntax of the CREATE FUNCTION statement is:

CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [AS ] [ type_schema_name. ]
Parameter_data_type] }
[ = default ] }
[, …n ]
]
)
RETURNS return_data_type
[WITH <function_option> [ , . . .n ] ]
[ AS ]
BEGIN
Function_body
RETURN expression
END
[;]
Where,

• Schema_name is the name of the schema to which the UDF belongs.
• Function_name is the name of the UDF. Function names must comply with the rules for identifiers and must be unique within the database and to its schema.
• @parameter_name is a parameter in the UDF. One or more parameters can be declared.
• [type_schema_name.] parameter_data_type is the data type of the parameter, and optionally the schema to which it belongs.
• [=default ] is a default value for the parameter.
• Return_data_type is the return value of a scalar user-defined function.

Calling a Procedure from another Procedure: SQL

At times, you might need to use the values returned by a procedure in another procedure. For this, you can execute or call one procedure from within another procedure.

A procedure that calls or executes another procedure is known as the calling procedure, and the procedure that is called or executed by the calling procedure is termed as the called procedure. You can also execute a procedure from another procedure if you need to use the functionality provide by one into another.

Consider the previous example where the prcGetEmployeeDetail procedure returns the employee details for a given employee ID. You can create the prcDisplayEmployeeStatus procedure, which accepts the employee ID of an employee as input and displays the department name and shift ID where the employee is working along with the manager ID and the title employee. To perform this task, you need to call the prcGetEmployeeDetail procedure from the prcDisplayEmployeeStatus procedure, as shown in the following statement:

CREATE PROCEDURE prcDisplayEmployeeStatus @EmpId int
AS
BEGIN
DECLARE @DepName char (50)
DECLARE @ShiftId int
DECLARE @ReturnValue int
EXEC @ReturnValue = prcGetEmployeeDetail @EmpId,
@ DepName OUTPUT,
@ ShiftId OUTPUT
IF (@ReturnValue = 0)
BEGIN
PRINT ‘The details of an employee with ID: ‘ + convert (char (10), @EmpId)
PRINT ‘shift ID: ‘ + convert ( char (1), @shiftId)
SELECT ManagerID, Title FROM HumanResources.Employee
WHERE EmployeeID = @EmpID
END
ELSE
PRINT ‘No records found for the given employee’
END

To execute the preceding code, you need to execute the following statement:
EXEC prcDisplayEmployeeStatus 2

SQL Server provides a function, @@ROWCOUNT, which return the number of rows affected by the last statement. You can use this statement in the IF construct to check the result of the last statement that executed.

How to Return values from Stored Procedure: SQL

Similar to providing input values to the procedures at run time, you can also return values as output from the procedures. The values can be returned to the calling application through output parameters. To specify a parameter as the output parameter, you can use the OUTPUT keyword.

The OUTPUT keyword has to be specified in both the CREATE PROCEDURE and the EXECUTE statement. If the OUTPUT keyword is omitted, the procedure will be executed but will not return any value.

The syntax of the declaring an output parameter using the OUTPUT keyword is:

CREATE PROCEDURE procedure_name
[
{ @parameter data_type} [OUTPUT]
]
AS
Sql_statement […n]

• @parameter data_type [OUTPUT] allows the stored procedure to pass a data value to the calling procedure. If the OUTPUT keyword is not used, then the parameter is treated as an input parameter.

You can also return values from the stored procedure by using the RETURN statement. The RETURN statement allows the stored procedure to return only an integer value to the calling application. The syntax of the RETURN statement is:

RETURN value
Where,

• Value is any integer. If a value is not specified, then the stored procedure returns a default value of 0 to specify failure and 1 to specify success.

Consider an example. You need to display the details of an employee whose employee ID has been provided as an input. For this, you need to create a procedure prcGetEmployeeDetail thet will accept employee ID as input and will return the department name and ID of the shift in which the employee works. You can create the procedure, as shown in the following statement:

CREATE PROCEDURE prcGetEmployeeDetail @EmpID int, @DepName char (50) OUTPUT, @ShiftID int OUTPUT
AS
BEGIN
IF EXISTS (SELECT * FROM HumanResources.Employee WHERE EmployeeID = @EmpID)
BEGIN
SELECT @DepName = d. Name, @Shiftid = h.ShiftID
FROM HumanResourcess.Department d JOIN
HumanResources.EmployeeDepartmentHistory h
ON d.DepartID = h.DepartmentID
WHERE EmployeeID = @EmpId AND h.Enddate IS NULL RETURN 0
END
ELSE
RETURN 1
END

The preceding procedure accepts the employee ID as an input parameter and returns the department name and shift ID as output parameters. The procedure first checks the existence of the given employee ID. Id it exists, the procedure returns and integer value 0 along with the required details.

How to Create Parameterized Stored Procedure: SQL

At times, you need to execute a procedure for different values of a variable that are provided at run time. For this, you can create a parameterized stored procedure. Parameters are used to pass values to the stored procedure during run time. These values can be passed by using standard variables.

The parameter that passes the values is defined as input parameter. A stored procedure has the capability of using a maximum of 2100 parameters. Each parameter has a name, data type, direction, and a default value.

The following example creates a stored procedure displaying the employee ID, the login ID, and title of the employees that have the same title provided as an input during

Execution:

CREATE PROC prcListEmployee @title char (50)
AS
BEGIN
PRINT ‘List of Employees’
SELECT EmployeeID, LoginID, Title
FROM HumanResources.Employee
WHERE Title = @title
END

Execute the stored procedure, prcListEmployee, by using the following statement:

EXECUTE prcListEmployee ‘Tool Designer’

While executing the stored procedures, you can also provide the values for the parameters by explicity specifying the name and value of the parameter. In the previous example, you can also pass the parameter value by using the name of variable, as shown in the following SQL statement:

EXECUTE prcListEmployee @title = ‘Tool Designer’