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’

Guidelines to Create and Execute Stored Procedure: SQL

To create stored procedure database developer have to keep some points in mind. The following points need to be considered before creating a stored procedure:

• You cannot combine the CREATE PROCEDURE statement with other SQL statements in a single batch.
• You must have the CREATE PROCEDURE permission to create a procedure in the database and the ALTER permission on the schema, where the procedure is being created.
• You can create a stored procedure only in the current database.
• After creating a stored procedure, you can execute the procedure. You can also alter the procedure definition or drop it, if not required.

Executing a Stored Procedure

A procedure can be executed by using the EXEC PROCEDURE statement. The syntax of the EXEC PROCEDURE statement is:

EXEC | EXECUTE PROCEDURE proc_name AS [ { LOGIN|USER} = ‘name’]
Where,

• Proc_name, is the name of the procedure you need to execute.
• LOGIN|USER specifies the name of the login of another user on the same database server or another user in the same database that you need to impersonate. While executing a procedure you can impersonate the rights of another user or ligin to execute the procedure. This allows a user who does not have permission to execute a procedure to execute it using the permissions assigned to other users.

Consider an example. Robert has created a stored procedure named DispEmpDetail that displays the details of the employees. Kim needs to execute the procedure but does not have the execution rights. In such a case, Kim can use the rights of Robert to execute. For this, Kim needs to impersonate Robert.

You can execute the stored procedure, prcDept, as shown in the following statement:
EXEC PROCEDURE prcDept

Altering Stored Procedure

A stored procedure can be modified by using the ALTER PROCEDURE statement. The syntax of the ALTER PROCEDURE statement is:

ALTER PROCEDURE proc_name

You can alter the stored procedure, prcDept, as followis:

ALTER PROCEDURE prcDept
AS
BEGIN
SELECT DepartmentID, Name FROM HumanResources.Department
END
In the preceding code, the DepartmentID attribute will be displayed along with the department name.

Dropping a Stored Procedure

You can drop a stored procedure from the database by using the DROP PROCEDURE statement. The syntax of the DROP PROCEDURE statement is:

DROP PROCEDURE proc_name

You cannot retrieve a procedure once it is dropped. You can drop the prcDept stored procedure by using the following statement:

DROP PROCEDURE prcDept