Interview Question of the Week #013 – Stored Procedure and Its Advantages – How to Create Stored Procedure

I have heard this interview question so often that I was once caught yawning while a co-interviewer asked it. The question is old, but a good answer still separates what a stored procedure can do from what it does automatically.

Question: How do you create a stored procedure, and what are its advantages?

Answer: Create a named routine in a database, then call it with EXEC. Here is the smallest useful example. Run it in a scratch database where dbo.MyFirstSP does not already exist.

CREATE PROCEDURE dbo.MyFirstSP
AS
BEGIN
    SET NOCOUNT ON;
    SELECT GETDATE() AS CurrentDateTime;
END;
GO

EXEC dbo.MyFirstSP;
GO

The call returns one row with the server’s current date and time. GO is an SSMS batch separator, so the procedure definition is sent as its own batch. In a real procedure, parameters describe inputs and the returned columns form a contract for the caller.

The advantages I would name in an interview are reusable logic, a stable interface for applications, fewer client round trips when one call performs related work, and controlled access when permissions and ownership are configured correctly. A suitable execution plan can be reused after the first execution. The procedure is not precompiled merely because it was created, and it does not make concatenated dynamic SQL safe or several writes atomic without an explicit transaction.

Those qualifications matter more than reciting a long list of advertised benefits. Ask what the procedure actually reads, changes, returns, and permits before calling it an improvement.

Original supporting examples

• original article over here

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.