difference

Question

Post reply

difference

bulu4u2000

Newbie

Points: 5
More actions
April 29, 2008 at 3:38 am

#63675

hi...
can you explaim me what is the difference between function and store procedure with example......

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

rajankjohn SSCertifiable Points: 5061 More actions · Answer 1

rajankjohn

SSCertifiable

Points: 5061

April 29, 2008 at 3:55 am

#808207

please check BOL for the same

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 2

[font="Verdana"]

1. In SProc you can write Select, Insert, Update, Delete statements where in Functions only Select statements can be return.

2. SProc can not be called in Select statement where as Fucntions can.

...

Hopes some experts will update both of us except these two differences. 🙂

Thanks in advance,

Mahesh

[/font]

MH-09-AM-8694

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 3

You both really need to make a trip to Books Online... and, just to be sure, you can do INSERTs, UPDATEs, and DELETEs on Table Variables in a UDF... just not on external tables.

Seriously, Books Online...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 4

Here are two links, I believe they can help.

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1063700,00.html

http://www.go4expert.com/forums/showthread.php?t=329

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 5

[font="Verdana"]

... and, just to be sure, you can do INSERTs, UPDATEs, and DELETEs on Table Variables in a UDF... just not on external tables.

Jeff, I was talking about regular tables only. 🙂 Coz I tried to write Insert, Update, Delete statement under Create Function ... statement, however it won't allowed me.

Mahesh

[/font]

MH-09-AM-8694

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 6

[font="Verdana"]

SQL ORACLE (4/29/2008)
Here are two links, I believe they can help.
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1063700,00.html
http://www.go4expert.com/forums/showthread.php?t=329

Hi, in the first URL you have given, Author has mentioned

You'll also find that although a SPROC can be used in an XML FOR clause, a UDF cannot be.

But if you will go through the below link, you can use XML in function. (It might be new feature in SQL 2005)

http://www.sqlservercentral.com/Forums/Topic484780-145-1.aspx

Mahesh

[/font]

MH-09-AM-8694

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 7

Mahesh Bote (4/30/2008)
[font="Verdana"]
... and, just to be sure, you can do INSERTs, UPDATEs, and DELETEs on Table Variables in a UDF... just not on external tables.
Jeff, I was talking about regular tables only. 🙂 Coz I tried to write Insert, Update, Delete statement under Create Function ... statement, however it won't allowed me.
Mahesh
[/font]

Heh... ok... just making sure the OP knew for sure... 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)