Hi all
Trying to figure out what you use ms sql functions for. I understand stored procedures and how to create them. the question is what is the real purpose of a ms sql function considering everything i have read so far makes me think that there is no valid use for them. You can do almost everything that a function does but in a stored procedure.
If somebody can give me a good examplle of a sql function i would appreciate it very much.
thanks
I use functions as a way to seperate cetain logic, usually so I can use it in several stored procedures. To give you an example, I often pass a comma delimited list of values to stored procedures as a varchar sql parameter. I created a function that splits that string on the delimiter (comma) and puts the values into a table, which I then use as part of the criteria for my select statement. So it all works out like this:
SELECT * FROM myTable
WHERE ApplicationID IN (Select value from dbo.f_split(@.mylist))
|||
wstevens@.vodamail.co.za:
You can do almost everything that a function does but in a stored procedure.
True. But you have to call a stored procedure with parameters and an exec statement, and then you have to declare those parameters as output if you want a return value that is anything other than an int.
A function can return any datatype. It is also easier to call a simple function during a SELECT statement than it would be a stored procedure, i.e. ISNULL, CAST, or CONVERT. If you have specific functions that you need to do in your database, and they are required in numerous places (more than 1), then a function may be a way to go.
An easy one could be GetUserName, to which you pass a UserID. The function could then concatenate the first name and last name together with a space in between. If you want to get even more fancy, it could add a prefix (Mr.) and a suffix (, Jr.). The function returns the complete user name without you having to implement the logic for concatenating and adding spaces, which allows it to easily be used in the select query for a user.
Another nice thing about a function is that it helps encapsulate and centralize the logic, just as the previous example showed us. What if you decide later you don't want the prefix and suffix? You have just one place to make the change. That's better than trying to remember every place you used ([Prefix] + ' ' + [FirstName] + ' ' + [LastName] + ' ' + [Suffix]) AS [UserName].
|||Hi
What u are saying with using the example of GetUserName is the function is called on its own and not via a Stored Procedure. So it retrieves a record and formats the output something like below;
strUsername = "Mr" & " " & [Name] & " " & [Surname]
output = Mr Name Surname
This is a basic formating of a 2 returned values. I understand that it makes it easier to change a suffix and a prefix from one place, but im sure there must be a more powerful use for functions. Can you give me an example of something which has to use a function as there is no other way. For example a stored procedure that requires a function to complete its execution.
|||
Hi
So what u are saying is you are passing an array of values from the function to the stored procedure. Can u possibly show me a full example of your function and the stored procedure. It would help if i can see the code.
thx
|||There are a number of different functions, each of which would have a different use.
Scalar functions:
SELECT dbo.BuildName(First,Middle,Last,Prefix,Suffix) As FullName
FROM MyTableWithNamesInIt
WHERE Last LIKE 'A%'
Of course that function would take all the parameters building a nicely formated name field. Something like Prefix+" "+First+" "+CASE WHEN ISNULL(Middle,'')<>'' THEN LEFT(MIddle,1)+'.' ELSE '' END+' '+Last+' '+Suffix would be pretty close unless prefix was null (or empty) in which case you would have a space at the beginning. Or middle didn't exist, then you'd have two spaces in the middle of the name, etc.etc. How would you accomplish the same thing with a stored procedure if you had many places where you had to build a name from multiple parts in many different queries?
If you realize that functions like LEFT(), RIGHT(), TRIM(), SUBSTRING(), ISNULL(), etc are scalar values functions, then you quickly understand how useful they can be.
Another example is table-valued functions where you can something like this:
SELECT *
FROM Orders o
JOIN dbo.Split(@.ListOfOrders) t1 ON o.ID=t1.ID
Of course @.ListOfOrders is a comma-delimited string of orders id's, like '1,2,3'. Yes, you can make a stored proc to retrieve a list of order id's as a comma-delimited list. But what if you need to do that for orders, receipts, customers, stores, and employees?
Table-valued functions can be very similiar to views with parameters as well. So you can do something like:
SELECT *
FROM dbo.CompletedOrdersThatWereNotReturnedBy('1/1/2007')
Of course that's not a great example, but they do come in very handy when you want to encapsulate some complex logic for reuse by others.
|||
A good example of a useful table-valued function could be... GenerateNumbers(Seed,Limit,Increment). So then you could grab every 3rd something like:
SELECT *
FROM Somethings s
JOIN dbo.GenerateNumbers(1,1000,3) gn on s.ID=gn.ID
In that example, GenerateNumbers would return a table of numbers (1,4,7,10,etc).
Another use would be for insuring that there always exists atleast one row for each month by generating rows 1-12 and using that as part of an outer join.
|||Hi
Would i be correct if i tried the following.
Create a function that can handle paging for a datalist. Call the function from a custom class. using a objectdatasources. If there is no page value posted back then it assumes that this is the frist call to the function. I then have a next and previous button. if I select next it calls the function and passed the value next to the function which intern checks the last page number and moves to the next page returning those values. If the value passed is of a null nature it then automatically returns to the first page .
