Sunday, 31 July 2011

Stored procedures vs. functions

1. In functions you  cant use DDL , DML smts (like Insert and Update) which is possible in SPs
2. You can use function output in select smts (like Select fnTest(), a from test a)
3  You can use the function output in joins (like test a LEFT OUTER JOIN fnTest() b )
4. Sp can return multiple result-set, function can return only one resultset
5. Function can accept less parameter than procedure
6. Function can not return XML output
.

Stored Procedures are basically compiled which results in faster execution. Once executed the results are cached thus resulting in better performance  more you use it. This is not the result in case of User functions.


Stored procedures are called independently, using the EXEC command, while functions are called from within another SQL statement.

Stored procedure allow you to enhance application security by granting users and applications permission to use stored procedures






create procedure sp_GetAllUsers
@ContactCode Char(12)
As
Select * from [OBXKites] .[dbo] .Contact Where  [OBXKites] .[dbo] .Contact.ContactCode=@ContactCode

exec sp_GetAllUsers 102






create function dbo.getContactName(@ContactCode char(15))
returns varchar(50)
Begin
Declare @name varchar(50)

 Select @name = FirstName +LastName  from [OBXKites] .[dbo] .Contact Where  [OBXKites] .[dbo] .Contact.ContactCode=@ContactCode
return @name
end


select dbo.getContactName('102');