Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 UDF or Stored procedure

Author  Topic 

LePrezAtWork
Starting Member

16 Posts
Posted - 2004-04-07 : 18:08:12
This is a simple question that might generate a lot of post... but...

I currently planning to convert an entire application that was done previously in MS Access. I currently don't know what the "frontend" of the app will be (maybe web with ASP or JSP, or VB, or pure Java app) but I'm pretty shure that the database will be in MS SQL because we already have the licenses for that.

I need to know if is there is any recommendation that you can give me about using UDF or Stored procedures. Does one is better than the other?

Sorry for my bad english

Thanks guy's

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-04-07 : 19:28:46
http://www.sqlteam.com/item.asp?ItemID=979

Tara
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2004-04-07 : 19:53:15
They do different things.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts
Posted - 2004-04-07 : 20:09:30
So far in my career, I have not had a compelling reason to create a UDF, although I can see how they'd be handy in other situations. But Stored Procedures are a MUST!

I do all application access via stored procedures, so all INSERT, SELECT, UPDATE, and DELETEs are in stored procedures. And whenever a Transaction is necessary for data integrity, that definitely needs to be in there. Functions on the other hand are useful for (among other things) returning calculated results like the "last day of the month" or things like that. UDFs can be powerful, but I'd expect you will only create them when you have defined a specific need that they'll meet. I think sprocs are more general-use.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2004-04-07 : 20:20:05
A common use is if you want to return a csv string of values per row (e.g. list of grouops a user is in) then a udf to return the csv string for a user_ID makes the query simple.
The UDF is a single statement (plus the variable declaration and return) and is just a call in the select.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-04-07 : 20:22:25
The most common UDF that we use is to convert between timezones. Our servers use GMT as the timezone, but users want to see the datetime values in their user-defined timezone.

Tara
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts
Posted - 2004-04-08 : 02:25:57
We use functions quite a bit where I work now.

They're pretty useful for:

1. Data scrubbing.
--You apply the same rules to a certain type of field such as money every time you bring it over.

2. Forumulas.
--You don't have to write the same forumula over and over and over.

3. Table functions.
--If you pull the exact same complex join a lot using one criteria different, you can wrap it up and put it in a table function.
--You have to be really, really careful with these. They can kill you.

4. Lookups.
--You pass in a lead number and it passes you back corresponding application and loan number for example.

Other than that, we use stored procedures for everything.


MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

LePrezAtWork
Starting Member

16 Posts
Posted - 2004-04-08 : 16:08:40
Your comments are interesting, and as i see most of you are using UDF only for smaller requests, data integrity or to return data in the form of a table. Do you think that if we adopt the following basics rules we will have someting that lead us to a stable and performing system?

- No direct table access by the client software
- All single data required by the client software is returned by sprocs
- All multiple data required by the client software is returned by UDFs
- INSERT, UPDATE or DELETE will be done by sprocs

What do you think about this?
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts
Posted - 2004-04-08 : 16:10:15
Can you explain this a little more?

- All multiple data required by the client software is returned by UDFs

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

LePrezAtWork
Starting Member

16 Posts
Posted - 2004-04-08 : 16:12:25
Yes sorry, by this i meen that if the client need data on the form of table or recordset we must use UDFs. This is more clear?

quote:
Originally posted by derrickleggett

Can you explain this a little more?

- All multiple data required by the client software is returned by UDFs

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

LePrezAtWork
Starting Member

16 Posts
Posted - 2004-04-08 : 16:15:47
Thanks Tara for the link!

quote:
Originally posted by tduggan

http://www.sqlteam.com/item.asp?ItemID=979

Tara
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2004-04-09 : 10:18:52
quote:
Originally posted by LePrezAtWork

...
Do you think that if we adopt the following basics rules we will have someting that lead us to a stable and performing system?

- No direct table access by the client software
- All single data required by the client software is returned by sprocs
- All multiple data required by the client software is returned by UDFs
- INSERT, UPDATE or DELETE will be done by sprocs
No offence man but I thought this to be quite funny I would think that creating a stable and perfoming system would need a slightly more detailed ruleset than that...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"
Go to Top of Page

LePrezAtWork
Starting Member

16 Posts
Posted - 2004-04-09 : 10:22:33
I know that we must have more detailed rules but do you think that these are correct as basic rules, in general term speaking?

quote:
Originally posted by Lumbago

quote:
Originally posted by LePrezAtWork

...
Do you think that if we adopt the following basics rules we will have someting that lead us to a stable and performing system?

- No direct table access by the client software
- All single data required by the client software is returned by sprocs
- All multiple data required by the client software is returned by UDFs
- INSERT, UPDATE or DELETE will be done by sprocs
No offence man but I thought this to be quite funny I would think that creating a stable and perfoming system would need a slightly more detailed ruleset than that...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2004-04-09 : 10:34:46
Well, there are guys here that are imensely more qualified to answer this than me but here are my two cents worth:

I belive that programming an app either frontend or backend is an iterative process that have demands that hardly ever fit in to any mole or ruleset made before you start. I belive in guidelines for programming, but there are no absolute rules for anything. It all depends on what you need to do and what gives the best performance in each individual case. That beeing said making guidlines and sticking to them to the best of your ability will give you a system that is easier to maintain and document and that is always good. But performancewise and stabilitywise you will need to do what's best in each individual case, at least that's the way I see it.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"
Go to Top of Page

LePrezAtWork
Starting Member

16 Posts
Posted - 2004-04-09 : 10:43:23
Very philosophical but realistic answer... I like it! As I think the development process will show us what is best. I will go with these basics rules and extend them along our development process. If any of you has other suggestions they are welcome!
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2004-04-09 : 11:01:35
One thing you need to kkep in mind is that UDF's only can return one single value, or a table variable. I tend to use UDF's only where I need to do some sort of conversion or mathematical operation within another select. I run a website where I need to display money in the users own currency and UDF's have been a very good friend in doing this. Instead of an "insane" join I do something like this:

SELECT Name, Points, dbo.DisplVolume(UserID, Volume) AS Volume FROM table

and the UDF would be like this:
CREATE  FUNCTION dbo.DisplVolume (@UserID int, @Volume decimal(10, 2))
RETURNS decimal(10, 2)
AS

BEGIN
DECLARE @newVolume decimal(10, 2)

  SET @newVolume = (SELECT Rate * @Volume FROM exrates a, users b
    WHERE a.Currency = b.Currency
    AND b.UserID = @UserID
    AND a.Updated = (SELECT MAX(Updated) FROM exrates 
      WHERE Updated <= GETDATE() AND Currency = b.Currency))

RETURN @newVolume
END
Created this on the fly so there might be some errors but you'll get the idea...
Go to Top of Page

LePrezAtWork
Starting Member

16 Posts
Posted - 2004-04-09 : 11:17:28
Hey, that's interesting! It's a cool example! What I also understand is that sprocs don't return table but I can define a sproc like this one :

CREATE PROCEDURE pr_Add_VoiceTerminal
@MAC varchar(20),
@Serial varchar(30),
@Cl_No varchar(14),
@IP varchar(15),
@Success bit OUTPUT,
@EqID integer OUTPUT,
AS
BEGIN
INSERT INTO VOIP_VoiceTerminals (VT_MAC, VT_Serial, CL_No, VT_IP)
VALUES (@MAC, @Serial, @Cl_No, @IP)
IF @@ERROR <> 0
BEGIN
SET @Success = 0
SET @EqID = 0
END
ELSE
BEGIN
SET @Success = 1
SET EqID = (SELECT VT_ID FROM VOIP_VoiceTerminals WHERE
VT_MAC = @MAC, VT_Serial = @Serial, CL_No = @ClNO, VT_IP = @IP)
END
END

So I can get the results from VB for the excution of that using parameters of the ADODB command.execute... I am wright? This is a good way of doing it?
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2004-04-09 : 12:21:17
Yes this is a good way of doing it. I usually tend to have a normal select at the bottom of the procedure and use a recordset to retrieve what's in it from my ASP, but if you only need to return a few values using the command-object is definetly the best way. I'm quite sure you can have a stored procedure return a table-variable also but it would probably only be usable from another stored procedure. You can easily run one sp from another and use the results from it...
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts
Posted - 2004-04-09 : 18:27:33
quote:
Originally posted by LePrezAtWork

Hey, that's interesting! It's a cool example! What I also understand is that sprocs don't return table...


NO! That is not correct. A stored procedure can return a recordset just fine. You can use the command object to setup the parameters you are passing, and then bring the resuts back into a recordset object in your code with something like

Set oRS = oCmd.Execute

Your rule about using UDFs when you need multiple values I believe to be incorrect, or at least not best practice. I would say that you should use UDFs ONLY when what you want cannot be done within a stored procedure or SELECT statement. There is a LOT of logic you can do within a stored procedure, and some pretty damn fancy stuff you can do just in a SELECT statement. For example, look at Jeff's Dynamic SQL answer over here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=34000

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]
Go to Top of Page

LePrezAtWork
Starting Member

16 Posts
Posted - 2004-04-13 : 09:35:47
quote:
Originally posted by AjarnMark

NO! That is not correct. A stored procedure can return a recordset just fine. You can use the command object to setup the parameters you are passing, and then bring the resuts back into a recordset object in your code with something like

Set oRS = oCmd.Execute


Hi Mark, my previous readings have let me thinking that was not possible but I have to be frank and say that I don't have tried it!

You say that I should use UDFs only for things that can't be done by sp or simple select... do you have an example of that? There is a list of things that can't be done by UDFs? Thanks!
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2004-04-13 : 09:46:24
Look up "CREATE FUNCTION" in books online, it will give you some good information on what you can and canot do using UDF's.
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site