April 8, 2009 at 10:23 pm
hi..
Difference between Stored procedure & User defined functions
April 8, 2009 at 11:21 pm
Stored procedure
A stored procedure is a program (or procedure) which is physically stored within a database. The advantage of a stored procedure is that when it is run, in response to a user request, it is run directly by the database engine, which usually runs on a separate database server. As such, it has direct access to the data it needs to manipulate and only needs to send its results back to the user, doing away with the overhead of communicating large amounts of data back and forth.
User-defined function
A user-defined function is a routine that encapsulates useful logic for use in other queries. While views are limited to a single SELECT statement, user-defined functions can have multiple SELECT statements and provide more powerful logic than is possible with views.
Differences
# UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
# UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
# Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
Tanx 😀
April 9, 2009 at 8:44 am
Check out the below link
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1063700_mem1,00.html
April 9, 2009 at 8:47 am
Another important difference is that procs can modify data, while UDFs can't.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 12, 2009 at 9:19 pm
Built-in functions that can return different data on each call are not allowed in user-defined functions. The built-in functions not allowed in user-defined functions are:
@@CONNECTIONS
@@PACK_SENT
GETDATE
@@CPU_BUSY
@@PACKET_ERRORS
GetUTCDate
@@IDLE
@@TIMETICKS
NEWID
@@IO_BUSY
@@TOTAL_ERRORS
RAND
@@MAX_CONNECTIONS
@@TOTAL_READ
TEXTPTR
@@PACK_RECEIVED
@@TOTAL_WRITE
Tanx 😀
April 12, 2009 at 10:14 pm
ya.. thanks to all
April 13, 2009 at 6:46 am
Eswin (4/12/2009)
Built-in functions that can return different data on each call are not allowed in user-defined functions. The built-in functions not allowed in user-defined functions are:@@CONNECTIONS
@@PACK_SENT
GETDATE
@@CPU_BUSY
@@PACKET_ERRORS
GetUTCDate
@@IDLE
@@TIMETICKS
NEWID
@@IO_BUSY
@@TOTAL_ERRORS
RAND
@@MAX_CONNECTIONS
@@TOTAL_READ
TEXTPTR
@@PACK_RECEIVED
@@TOTAL_WRITE
Some of these can be used in SQL 2005 UDFs. But definitely not in SQL 2000.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply