I used to learn about SQL, especially MySQL. I make these notes to guide me and others who want to transition to mssql. I usually summarized what I found and understood so far from browsing the internet.
My takeaways:
| Feature | Stored Procedure | Function |
|---|---|---|
| Purpose | Perform actions or tasks, can modify data | Return a single value or a table of values |
| Return Values | Can return multiple values or none | Must return a single value or table |
| Usage | Can be called from a trigger, another procedure, or an application | Can be used in SELECT, WHERE, and JOIN clauses |
| Parameters | Can have input, output, and input-output parameters | Can have only input parameters |
| Transactions | Can use transactions (COMMIT, ROLLBACK) | Cannot use transactions |
| Error Handling | Can use error handling (TRY, CATCH) | Cannot use error handling |
| Modify Data | Can modify data (INSERT, UPDATE, DELETE) | Cannot modify data directly |
Stored Procedures
- Use stored procedures for complex business logic and tasks that modify data.
- Use error handling and transactions for better control over data consistency.
- Utilize parameterization to avoid SQL injection attacks.
- Follow naming conventions and proper documentation for easier maintenance.
Functions
- Use functions for calculations and data manipulation that return a single value or table.
- Keep functions simple and focused on a specific task.
- Avoid using functions that have side effects or modify data.
- Utilize scalar functions for single value returns and table-valued functions for returning tables.
In Short
Think of stored procedures as a set of instructions you give to the database to perform tasks, like updating or deleting data. They can be called by other parts of your application or database to execute these tasks. Functions, on the other hand, are more like formulas or calculations that you can use in your queries to retrieve or manipulate data. They return a single value or a table of values, and you can use them in various parts of your SQL queries, such as filtering or combining data.
Top comments (0)