October 7, 2004 at 1:17 am
Hi,
Please tell me what are the differences between stored procedure and user defined functions. This is a interview question. when i said sp is a precompiled sql statement, they said no and some more differences are there. what are those.
please tell me
bye
October 7, 2004 at 1:40 am
A stored procedure is more than just a precompiled SQL statement (and it's not ever fully compiled in the traditional programming sense). Here are two things that make a stored procedure "special" for us:
- Stored procedure execution plans do tend to stick around longer than ad hoc queries. Having the execution plan available means SQL Server can hopefully re-use it rather than taking the time to generate another one. I think that's what you mean by precompiled. However, if memory usage is extreme or the stored procedure execution plan isn't present because the stored procedure hasn't been executed since say the last time SQL Server was restarted, it will have to go through the process of generating a new execution plan.
- Stored procedures allow us to give users controlled access to tables and views without assigning permissions against those tables and views. If the owner of the stored procedure matches the owner of the object we're trying to access, ownership chaining is in effect. That's a huge security boost because within the stored procedure we can insert the proper logic to control how a user is able to work with the data in the tables.
As for the differences between stored procedures and user defined functions... well let's first start with their similarities. Stored procedures can return a result set like a table-valued user defined function. In addition, both are subroutines encapsulating one or more blocks of code. Finally, both can take inputs (parameters) when they are used.
Now the differences... a stored procedure is an object that can stand on its own. In other words, you can just execute a stored procedure. You must combine a UDF with something, such as a SELECT statement for it to be useful. Second, there are some statements allowed in a stored procedure that are not allowed in a UDF. Books Online discusses what is and isn't allowed for UDFs. Third, while default values can be set for UDF functions, in stored procedures they can simply be omitted and you get the default value but in the case of an UDF you must specify all parameters and if you want the default value for a parameter you must therefore declare that. Finally, stored procedures support output parameters and UDFs don't.
K. Brian Kelley
@kbriankelley
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply