Servers Categorization Of Stored Procedures Based On Input And Output Parameters In Sql in SQL
In SQL Server, stored procedures can be categorized based on their input and output parameters, which helps in understanding how they interact with the caller. Here's a breakdown of how stored procedures can be categorized from this perspective:
? 1. Input-Only Stored Procedures
Description: These procedures accept input parameters only and perform operations (e.g., insert, update, delete, calculations) based on the provided values.
Usage: Used to manipulate data or execute logic without returning data explicitly.
Example:
CREATE PROCEDURE InsertEmployee @Name NVARCHAR(100), @DepartmentId INTASBEGIN INSERT INTO Employees (Name, DepartmentId) VALUES (@Name, @DepartmentId);END
? 2. Output-Only Stored Procedures
Description: These procedures do not take any input but return data using output parameters.
Usage: Rare, but useful for retrieving system or static values.
Example:
CREATE PROCEDURE GetServerDate @CurrentDate DATETIME OUTPUTASBEGIN SET @CurrentDate = GETDATE();END
? 3. Input-Output (INOUT) Stored Procedures
Description: These procedures accept parameters that are both input to and output from the procedure.
Usage: Modify the value of a parameter and return it to the caller.
Example:
CREATE PROCEDURE AdjustSalary @EmployeeId INT, @Salary DECIMAL(10,2) OUTPUTASBEGIN SELECT @Salary = Salary * 1.10 FROM Employees WHERE EmployeeId = @EmployeeId;END
? 4. Mixed Input and Output Parameters
Description: These procedures take some parameters as input and return others as output.
Usage: Common for procedures that compute and return results based on input.
Example:
CREATE PROCEDURE GetEmployeeDetails @EmployeeId INT, @Name NVARCHAR(100) OUTPUT, @Department NVARCHAR(100) OUTPUTASBEGIN SELECT @Name = Name, @Department = Department FROM Employees WHERE EmployeeId = @EmployeeId;END
?? Categorization Summary Table
| Category | Input | Output | Use Case |
|---|---|---|---|
| Input-Only | ? | ? | Inserts, updates, deletes |
| Output-Only | ? | ? | System info, defaults |
| Input-Output (INOUT) | ? | ? | Modify and return values |
| Mixed Input & Output Params | ? | ? | Compute and return results |
? How to Query Stored Procedures and Parameters in SQL Server
To list stored procedures and their parameters, you can use system views like:
SELECT p.name AS ProcedureName, pr.name AS ParameterName, pr.parameter_id, pr.is_output, t.name AS DataTypeFROM sys.procedures pJOIN sys.parameters pr ON p.object_id = pr.object_idJOIN sys.types t ON pr.user_type_id = t.user_type_idORDER BY p.name, pr.parameter_id;This helps you dynamically audit or categorize procedures in a database.
Let me know if you'd like a script to generate categorized lists from your database or need help building a reporting view for this.
