So, continuing from yesterday with the SQL Server subject, it is time to introduce some nice ways of writing functions and procedures in SQL.
What is the idea, behind the functions and the procedures in SQL?
Pretty much, I would like to say that it has something to do with the traditional VBA definition of a function and procedure – the function returns a value and the procedure executes some code. It may print value while executing or it may change plenty of other values as well.
The deal in SQL Server is pretty much the same – generally, the functions should return values and the procedures should do something 🙂 Of course, if you are really willing to go against the flow you may do whatever you want with thees, but this is not the subject of this article.
So, lets start at last.
Procedure in SQL Server
I will work on the DB from here. The idea is to create a procedure, that displays the rows in the table Money, in which the value for “PaidPerCell” is less than the one entered in the procedure. E.g., if we write a query like “usp_Less 100000” we would get the following output:
It is obvious, that the MS SQL Server has done some simple query, selecting the values less than 100K. Here is how we have “told” the SQL Server what exactly to do, just execute the following as a query:
|
1 2 3 4 5 6 7 8 9 |
CREATE PROC usp_Less @number int AS SELECT * FROM Money m WHERE m.PaidPerCell< @number ORDER BY m.PaidPerCell ASC GO usp_Less 100000 GO |
Thus, once you enter this on SQL Server, whenever you write “usp_Less 200” you would get a similar result. This was the procedure.
Function in SQL Server
The idea with the function is similar. We have to declare it in SQL and we have to execute it as well. Lets see how this is done.
The idea is to write a simple function, telling us how much money would we have after a give period, concerning a given percentage of interest. Thus, something that gives us this:
Or with other words, if we have 5000 units before interest, the interest is 5% per year (interests are given always per year in Bulgaria) and the period is 1 month, then at the end of the period you would have 5020.83 units. Pretty trivial calculation, but let’s not forget that we are asking a SQL Server to do it, thus it is more challenging. Here comes the code:
|
1 2 3 4 5 6 7 8 |
CREATE FUNCTION ufn_CalculateAfterInterest(@sum money, @yearly_interest float, @months float) RETURNS money AS BEGIN RETURN (@sum * (@yearly_interest / 100)) * (@months / 12)+@sum END GO PRINT(dbo.ufn_CalculateAfterInterest(5000,5,1)) |
As I have already mentioned, the function returns a value. Thus, it may be used in a regular SQL expression as such. E.g. the following table can be generated as well:
The SELECT statement is also visible.
Pretty much, that is all, thank you for your interest at my site. 🙂