SQL Stored Procedures Interview Questions

A stored procedure is a precompiled collection of SQL statements saved in the database that can be executed as a single unit. You call it by name, optionally passing parameters, and it runs the enclosed logic on the server side. Stored procedures reduce network traffic since you send one call instead of multiple queries, and the database caches the execution plan for better performance.

CREATE PROCEDURE GetEmployeesByDept
  @DeptId INT
AS
BEGIN
  SELECT name, salary
  FROM employees
  WHERE dept_id = @DeptId
  ORDER BY salary DESC;
END;

Stored procedures improve performance because the execution plan is compiled and cached on first run, so subsequent calls skip the parsing and optimization steps. They reduce network traffic by sending a single procedure call instead of multiple SQL statements. They also improve security by allowing users to execute the procedure without direct access to the underlying tables, and they help prevent SQL injection when used with parameterized inputs.

You create a stored procedure using the CREATE PROCEDURE statement, followed by the procedure name, parameters, and the SQL logic inside a BEGIN/END block.

CREATE PROCEDURE GetActiveUsers
  @MinAge INT = 18
AS
BEGIN
  SELECT id, name, email
  FROM users
  WHERE is_active = 1
    AND age >= @MinAge;
END;

-- Call it
EXEC GetActiveUsers @MinAge = 25;

A stored procedure can perform any database operation (SELECT, INSERT, UPDATE, DELETE) and does not have to return a value. A user-defined function must return a value (scalar or table) and cannot modify database state (no INSERT, UPDATE, or DELETE). Functions can be used inside SELECT statements and WHERE clauses, while stored procedures cannot. Stored procedures support output parameters and error handling with TRY/CATCH, while functions are more limited but composable within queries.

Use RAISERROR or THROW to raise custom errors from a stored procedure. RAISERROR lets you specify a message, severity level (0-25), and state. THROW was introduced in SQL Server 2012 and is simpler, automatically setting severity to 16. For reusable custom errors, you can define them with sp_addmessage and reference them by ID.

-- Using THROW (SQL Server 2012+)
THROW 50001, 'Invalid employee ID provided.', 1;

-- Using RAISERROR
RAISERROR('Order amount cannot be negative.', 16, 1);

Stored procedures offer several advantages: they reduce network traffic because you send one procedure call instead of multiple SQL statements, they improve performance through execution plan caching and reuse, and they centralize business logic making maintenance easier. They also improve security by letting you grant users permission to execute a procedure without granting direct access to underlying tables. On the downside, stored procedures are harder to debug and test compared to application code, and they tie your business logic to a specific database platform making migrations more painful. Overusing them can also lead to a situation where critical logic is scattered across the database and hard to version control alongside your application code.

CREATE PROCEDURE GetEmployeeByDept
    @DeptId INT
AS
BEGIN
    SELECT name, salary FROM employees WHERE department_id = @DeptId;
END;

A recursive stored procedure is one that calls itself during its own execution, typically to process hierarchical or tree-structured data like an org chart or bill of materials. SQL Server allows a stored procedure to call itself up to a maximum nesting level of 32 by default. You need a proper termination condition to avoid infinite recursion. In modern SQL Server, CTEs with the RECURSIVE pattern are often preferred for this kind of work, but recursive stored procedures are still valid for more complex multi-step logic.

CREATE PROCEDURE GetEmployeeHierarchy
    @ManagerId INT,
    @Level INT = 0
AS
BEGIN
    SELECT name, @Level AS level FROM employees WHERE manager_id = @ManagerId;

    -- Recursive call for each subordinate
    DECLARE @EmpId INT;
    DECLARE cur CURSOR FOR SELECT id FROM employees WHERE manager_id = @ManagerId;
    OPEN cur;
    FETCH NEXT FROM cur INTO @EmpId;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC GetEmployeeHierarchy @EmpId, @Level + 1;
        FETCH NEXT FROM cur INTO @EmpId;
    END;
    CLOSE cur;
    DEALLOCATE cur;
END;

The native system stored procedure for that is sp_MSforeachdb. It accepts a command string where a question mark acts as a placeholder for each database name and executes that command against every database on the instance. It is an undocumented procedure that Microsoft does not officially support, so it can occasionally skip databases under load, but it works in practice and is widely used for administrative tasks like checking database sizes or running maintenance commands.

-- Print the name and size of every database on the server
EXEC sp_MSforeachdb 'USE [?]; SELECT DB_NAME() AS db_name, SUM(size * 8 / 1024) AS size_mb FROM sys.database_files;';

A user-defined function (UDF) is a reusable routine you write yourself to encapsulate logic that can return a value or a table. Unlike stored procedures, UDFs can be called directly inside a SELECT statement, which makes them handy for calculations or transformations you need inline. They accept input parameters and always return a result, either a scalar value or a table. The key restriction is that UDFs cannot have side effects like modifying data or calling procedures that do.

CREATE FUNCTION dbo.GetFullName(@first VARCHAR(50), @last VARCHAR(50))
RETURNS VARCHAR(100)
AS
BEGIN
    RETURN @first + ' ' + @last;
END;

-- Usage inside a query
SELECT dbo.GetFullName(first_name, last_name) FROM employees;