Question

Explain Stored Procedure and Function with an example.

Answer 1

Stored Procedure and Function are two important database programming constructs that help in reducing code redundancy, increasing efficiency, and improving performance.

Stored Procedure:

A Stored Procedure is a set of SQL statements that is precompiled and stored in the database server. It is a reusable piece of code that can be called multiple times from different programs or applications. Stored procedures are used to encapsulate complex business logic or tasks that involve multiple database operations.

Here is an example of a simple Stored Procedure that takes in a parameter @category and returns all the books that belong to that category:

    CREATE PROCEDURE sp_GetBooksByCategory
        @category VARCHAR(50)
    AS
    BEGIN
        SELECT BookID, Title, Author, ISBN
        FROM Books
        WHERE Category = @category
    END

In this example, the Stored Procedure sp_GetBooksByCategory takes in a single parameter @category which is used to filter the Books table. When the Stored Procedure is executed, it will return all the books that belong to the specified category.

Function:

A Function is similar to a Stored Procedure in that it is a reusable piece of code, but it differs in that it returns a single value or a table variable. Functions are used to encapsulate frequently used expressions or calculations that are required in multiple parts of an application or query.

Here is an example of a simple Function that takes in two parameters @x and @y and returns their sum:

    CREATE FUNCTION fn_Add
        (@x INT, @y INT)
    RETURNS INT
    AS
    BEGIN
        DECLARE @result INT
        SET @result = @x + @y
        RETURN @result
    END

In this example, the Function fn_Add takes in two parameters @x and @y, adds them together and returns the result. When the Function is executed, it returns a single value which is the sum of the two input parameters.

Both Stored Procedures and Functions can be called from within other queries, applications or Stored Procedures. They provide a way to encapsulate complex logic or frequently used calculations, making it easier to write efficient, maintainable and reusable code.