| Author |
Topic |
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
 Posted - 2004-11-19 : 01:36:21
|
| when do you use a function and when do you use an sproc? both seems to be the same and can handle the same load. thoughts anyone?--------------------keeping it simple... |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-11-19 : 06:18:48
|
sprocs can change db schema functions can't...functions are used for row by row procesing (when you want a result for each row), sproc's can't.those are two diff's that are first on my list...Go with the flow & have fun! Else fight the flow  |
 |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-11-19 : 07:40:00
|
Also, functions can return table variables so they are great to use on parameters that are lists and the like.and what spirit said of course... Corey |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-11-19 : 08:07:41
|
| Put it this way.... DateAdd is a function.Could you implement the same functionality with a sproc (i.e. you can call it inline) ?Damian |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-19 : 08:09:57
|
Sorry, %USER_NAME%, we don't do answers to homework questions on SQL Team  The Management |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-11-19 : 08:13:27
|
OUCH  Damian |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-19 : 08:34:04
|
| Hehehehe! |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-11-20 : 11:09:00
|
quote:
Originally posted by Kristen
Sorry, %USER_NAME%, we don't do answers to homework questions on SQL Team The Management
cmon guys, this me, Jen, not the pretty one ofcourse, he he he...it's my 10th month as DBA and with SQL. While reviewing some sp's and udf's in one project, there was an sproc and a udf that has the same "function". so this made me wonder, which will perform better?execution plan showed the same.ah yes, tables, that's one difference, except if you'll save the sp resultset to another variable.yap, i think that's another difference, inline call.ok, so the fog is clearing out, hope you can share more of your "experience-based" views on this topic. I'd like to determine if a set of commands i have in mind will be better of as an sp or udf.i kinda got hooked up on the idea of C function and procedures.  --------------------keeping it simple... |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-11-20 : 11:10:56
|
quote:
Originally posted by Kristen
Hehehehe!
you are bad kristen, bad...  --------------------keeping it simple... |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-11-20 : 11:15:03
|
quote:
Originally posted by spirit1
sprocs can change db schema functions can't...Go with the flow & have fun! Else fight the flow
really? i'll try this, my policy is to test before believing. he he he, no offense meant ofcourse...quote:
functions are used for row by row procesing (when you want a result for each row), sproc's can't.
hmm... what do you mean?--------------------keeping it simple... |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-11-20 : 11:46:31
|
| i meant inline you know:select col1, dbo.MyFunc(col2), ...from ...i just couldn't remember the word BOL explains it preety nice and you are preety how's badminton going, any pulled muscles yet?Go with the flow & have fun! Else fight the flow |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-11-20 : 11:53:58
|
| oh yeah nigel once said the functions have some bugs... this is the link he gave me:http://support.microsoft.com/default.aspx?scid=kb;en-us;819264google probably gives more...for instance we have a few queries that all have a few of the tables that are the same in the joins. so i put those joined tables in a function that returns a table and i just use the function in all the queries. so there's just one place to fix it if it needs to be fixed . i guess that's a bit of C++ functionality you need, no?Go with the flow & have fun! Else fight the flow |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-20 : 12:28:27
|
| "you are bad kristen, bad..."Well, of course if I actually knew the answer I would have given you some useful comment. More articulate and learned people than me are doing just nicely at the moment ... :-)Kristen |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2004-11-21 : 14:54:55
|
| You smooth talker Kristen ;)steveTo alcohol ! The cause of - and solution to - all of life's problems |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-11-21 : 21:31:45
|
quote:
Originally posted by spirit1
i meant inline you know:select col1, dbo.MyFunc(col2), ...from ...i just couldn't remember the word BOL explains it preety nice and you are preety how's badminton going, any pulled muscles yet?Go with the flow & have fun! Else fight the flow
thanks for the information guys, i think i have crossed the path of enlightenment with regards to SPs and UDFs.btw, no pulled muscles, great for my insomnia, i'm going to try taekwondo or taebo next. got bored with the game, it was fun though.--------------------keeping it simple... |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-11-21 : 21:34:18
|
quote:
Originally posted by spirit1
for instance we have a few queries that all have a few of the tables that are the same in the joins. so i put those joined tables in a function that returns a table and i just use the function in all the queries. so there's just one place to fix it if it needs to be fixed . i guess that's a bit of C++ functionality you need, no?Go with the flow & have fun! Else fight the flow
that was clever thinking, especially to avoid the massive typing, he he he, logic=creativity--------------------keeping it simple... |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-11-21 : 21:36:23
|
quote:
Originally posted by Kristen
"you are bad kristen, bad..."Well, of course if I actually knew the answer I would have given you some useful comment. More articulate and learned people than me are doing just nicely at the moment ... :-)Kristen
ha ha ha, ok, you're absolved. --------------------keeping it simple... |
|
|
|
|
difference between function and sproc?