Click here to Skip to main content
15,867,756 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Recently, in a interview, i have been asked this question.
"Why use functions instead of stored procedure in sql"

I answered we cannot perform DML operations using functions, We can use it to retrieve data only.

But Interviewer asked me, Stored procedures could also retrieve data then why use functions?

I answered : Don't know, I only use Procedures.

I would like to know from all u guys, what u think about this questions.

Thanks
Posted
Updated 19-Aug-13 2:35am
v2
Comments
[no name] 19-Aug-13 8:33am    
Did they scream at you too when they asked you? Why are you screaming at us?
Thanks7872 19-Aug-13 8:35am    
Shouting removed.
GuyThiebaut 19-Aug-13 9:59am    
One issue with functions is that you cannot use dynamic sql within them.

 
Share this answer
 
Comments
Anto Reegan 12-May-14 8:21am    
Hi We can call SP in UDF. just have a look at this.<br>
<br>

CREATE PROCEDURE SampleSP<br>
AS<br>
SELECT top 1 name from sys.tables order by NEWID()<br>
GO<br>
 <br>
CREATE FUNCTION udf_SampleF ()<br>
returns nvarchar(100)<br>
as<br>
begin<br>
declare @name nvarchar(100)<br>
SELECT @name = name FROM OPENQUERY([.], 'KodyazSQL.dbo.SampleSP') query<br>
return @name<br>
end<br>
GO<br>
 <br>
select dbo.udf_SampleF()
Anto Reegan 12-May-14 8:23am    
Any query plz refer this

http://forums.asp.net/t/1804151.aspx?Can+we+Call+a+Stored+procedure+inside+a+function+
This is not exactly the same as Executing a Stored Procedure, but does the job.

Anyway, thanks for the information,
Tadit
If i am writing a sql expression and i want to compute a certain value within that sql expression then i can use a function.This would avoid the cluttering caused by using mathematical expressions within the sql expression.

eg :

CREATE OR REPLACE FUNCTION CALC_TAX (P_SAL IN EMPLOYEES.SALARY%TYPE)
RETURN NUMBER
IS
BEGIN
RETURN (P_SAL*.15);
END CALC_TAX;
/

SELECT EMPLOYEE_ID,LAST_NAME,SALARY,CALC_TAX(SALARY) FROM EMPLOYEES WHERE EMPLOYEE_ID = 101;

so here instead of calculating the tax within the sql expression we have done it using the help of a function!

however if i wanted to return more than one values then i would have certainly gone for procedures.
but if i used procedures i wouldn't have been able to use it within the select statement of my sql query. this is because the procedure is executed as a statement.

So in such scenarios as above functions would be a better choice.


However whether to use functions or procedures would depend purely on the context of the problem in hand.

Best of luck! and let us know the result of your interview.

Thank you.
 
Share this answer
 
Roughly speaking, you cannot call a stored procedure in a SELECT statement. I guess this could be a reason for using functions.
 
Share this answer
 
v2
You can not call a procedure in a select statement but you can call function in a select statement.
This is the major advantage of function over Procedure
 
Share this answer
 
 
Share this answer
 
v2
As already explained above, you CANNOT use SP in select statements.

Consider you want to compute a small math calculation, you dont want a SP for that. Just write the script in a tiny function and you can use it in a jiffy.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900