Views, Stored Procedures and Parameters in SQL Server
Quick Answer: A View provides a reusable way to retrieve data. A Stored Procedure is a saved SQL program that performs database operations. Parameters allow us to pass values into stored procedures and control their results.
1. Why Do We Need Views and Stored Procedures?
Imagine Ravi manages appointments for a hospital with 5,000 patients. Every day, he needs to check appointments for different doctors.
Every appointment contains patient details, doctor details and appointment status. Ravi needs the same information for different reports and application screens.
Should developers write the same SQL query repeatedly? Can we make database operations easier to reuse?
The problem without Views and Stored Procedures
We could write a separate SELECT query whenever Ravi requests appointments. Each application feature would repeat similar SQL statements.
Example 1: Retrieve scheduled hospital appointments directly
Assumes dbo.Appointments contains the three sample appointments shown below.
SELECT
AppointmentId,
PatientName,
DoctorName,
Status
FROM dbo.Appointments
WHERE DoctorName = N'Dr. Meera'
AND Status = N'Scheduled';
Expected output
AppointmentId PatientName DoctorName Status -------------- ------------ ----------- ----------- 201 Priya Dr. Meera Scheduled
This query works, but repeating it creates maintenance problems.
- Developers repeat the same SQL statements in multiple places.
- Changing a common query requires updating several locations.
- Passing different search values needs additional query logic.
- Managing database operations becomes harder as applications grow.
Now imagine Ravi supporting hundreds of doctors and thousands of appointments. Maintaining repeated SQL becomes difficult.
We need reusable database objects. We also need a simple way to pass different values.
The solution: Views and Stored Procedures
View = a saved SQL query that presents data like a virtual table.
Stored Procedure = a saved group of SQL statements that SQL Server can execute.
Parameter = a named value passed into a procedure or function.
Ravi's application can use a View for common appointment information. A Stored Procedure can retrieve appointments for a selected doctor.
Animation 1: How Views and Stored Procedures work together
Follow Ravi's request for Dr. Meera's scheduled hospital appointments.
Ravi selects Dr. Meera. The procedure passes that value to the query and returns her scheduled appointment.
2. Implement Views, Stored Procedures and Parameters in SQL Server
Let's implement Ravi's hospital appointment system using T-SQL.
CREATE VIEW = saves a reusable query definition inside the database.
CREATE PROCEDURE = creates a reusable database program.
EXEC = runs a stored procedure.
Create a View for scheduled appointments
A View lets developers reuse a common SELECT query. It does not normally store a separate copy of the selected records.
Create a parameterized Stored Procedure
We can create a procedure that accepts a doctor's name. The procedure reads the View and returns matching appointments.
Example 2: Create a View and parameterized Stored Procedure
Assumes dbo.Appointments contains the hospital appointment records used earlier.
CREATE VIEW dbo.vw_ScheduledAppointments
AS
SELECT AppointmentId, PatientName, DoctorName, Status
FROM dbo.Appointments
WHERE Status = N'Scheduled';
GO
CREATE PROCEDURE dbo.GetDoctorAppointments
@DoctorName NVARCHAR(100)
AS
BEGIN
SELECT *
FROM dbo.vw_ScheduledAppointments
WHERE DoctorName = @DoctorName;
END;
GO
EXEC dbo.GetDoctorAppointments
@DoctorName = N'Dr. Meera';
Notice that the View handles the common scheduled appointment filter. The procedure handles the doctor's name through @DoctorName.
The parameter value changes without changing the stored procedure definition. We can call the same procedure for another doctor.
Expected output
AppointmentId PatientName DoctorName Status -------------- ------------ ----------- ----------- 201 Priya Dr. Meera Scheduled
Important: A View cannot accept parameters directly. Use a WHERE clause or a parameterized Stored Procedure to filter its results.
3. Quick Summary
Without Views and Stored Procedures vs with them
| Without reusable objects | With Views and Procedures |
|---|---|
| Repeated SELECT statements. | Common queries can be saved as Views. |
| Repeated database operation logic. | Stored Procedures provide reusable operations. |
| Different values need separate query text. | Parameters accept different input values. |
| More repeated maintenance work. | Centralized database definitions simplify maintenance. |
The important terms to remember
- View: A saved SELECT query presented as a virtual table.
- Stored Procedure: A reusable SQL program stored in the database.
- Input Parameter: A value supplied when executing a procedure.
- EXEC: A command used to execute a stored procedure.
- Virtual Table: A table-like result based on a query definition.
4. Common Mistakes and Best Practices
1. Thinking Views always store data separately
A regular View stores its query definition, not a separate copy of its results.
Better approach: Understand the difference between regular Views and indexed Views.
2. Writing dynamic SQL unnecessarily
Building SQL strings with user input can introduce SQL injection risks.
Better approach: Use parameters and appropriate permissions for database operations.
3. Creating procedures without clear parameters
Procedures become difficult to reuse when their inputs are not clearly defined.
Better approach: Use meaningful parameter names, suitable data types and appropriate validation.
5. How Views and Stored Procedures Come Up in Interviews
Question 1: What is a View in SQL Server?
What the interviewer expects: Explain how Views simplify data access.
Strong answer: "A View is a saved SELECT query. In Ravi's hospital application, we can create a View for scheduled appointments and reuse it across different reports."
Question 2: What is a Stored Procedure?
What the interviewer expects: Explain reusable database logic.
Strong answer: "A Stored Procedure is a saved set of SQL statements. We can execute it whenever needed and pass parameters to control its operations."
Question 3: Can we pass parameters to a View?
What the interviewer expects: Understand the difference between Views and Procedures.
Strong answer: "No, a regular SQL Server View does not accept parameters. We can filter its records using a SELECT query or call it through a parameterized Stored Procedure."
6. Hands-On Practice
Beginner: School database
Create a View named vw_StudentDetails for student records. Create a Stored Procedure to retrieve students by class.
Expected result: Retrieve students by passing a class name.
Intermediate: Banking database
Create an account summary View. Create a procedure that accepts an AccountId and returns its details.
Hint: Use @AccountId INT as the input parameter.
Advanced: Hospital appointment system
Extend Ravi's hospital database with date-based appointment searches.
- Create a View for scheduled appointments.
- Create a procedure with doctor and appointment date parameters.
- Add an optional parameter for appointment status.
Challenge: Return the correct appointments using one reusable procedure.
7. Next Steps and Community
Previous article: SQL Server Window Functions – ROW_NUMBER, RANK, DENSE_RANK and NTILE
Next article: User-Defined Functions, Triggers and Dynamic SQL
For more details, read the official Microsoft documentation:
Try Ravi's hospital appointment example and add another doctor. Share your View and Stored Procedure implementations with other learners.