This week has been very interesting week. I have asked few questions to users and have received remarkable participation on the subject.
Q1) SQL SERVER – Puzzle – SELECT * vs SELECT COUNT(*)
Q2) SQL SERVER – Puzzle – Statistics are not Updated but are Created Once
Keeping the same spirit up, I am asking the third question over here.
Q3) When to use User Defined Function and when to use Stored Procedure in your development?
Personally, I believe that they are both different things ‑ they cannot be compared. I can say, it will be like comparing apples and oranges. Each has its own unique use. However, they can be used interchangeably at many times and in real life (i.e., production environment). I have personally seen both of these being used interchangeably many times. This is the precise reason for asking this question.
When do you use Function and when do you use Stored Procedure? What are Pros and Cons of each of them when used instead of each other?
If you are going to answer that ‘To avoid repeating code, you use Function’ ‑ please think harder! Stored procedure can do the same. In SQL Server Denali, even the stored procedure can return the result just like Function in SELECT statement; so if you are going to answer with ‘Function can be used in SELECT, whereas Stored Procedure cannot be used’ ‑ again think harder! (link).
Now, what do you say? I will post the answers of all the three questions with due credit next week.
Reference: Pinal Dave (https://blog.sqlauthority.com)
36 Comments. Leave new
I think my major reason to prefer stored procedure, it allow us to enhance application security by granting users and applications permission to use stored procedures instead of given the permission to access the tables.
It provides the ability to restrict user actions.
Very Nice tip
Do you know if Denali can also call a stored procedure from table constraints and type restrictions?
In the real world I have always though of functions as a method of extending functionality of sql server to suit your own needs. eg to check that the email actually looks like an email where the function solves a short well defined problem but may need to be changed later. Typically used on a per field bases at a time inside select statements.
I see stored procedures being more of a process of manipulating data and accessing it from applications.
Hello,
In a function you can’t:
– create temporary tables
– use transactions
– call a stored procedure
– use set commands(example: FORCEPLAN ON, ANSI DEFAULT NULL etc.), and the context inside the function is the same like the one from which is called.
– execute update,insert,delete statement
– have an ouput parameter like stored procedure have(this one can have an workaround no mather you need to return a single value or a set of values)
– multiple results(a stored procedure can return up to 1024, I think)
– call exec(‘some valid sql string’) statement(why should you want? but you can’t)
– use begin try end try begin catch end catch
I’m sure there exists also others differences!
Thanks pinal you are great.
Gatej,
You well said.
> call exec(‘some valid sql string’) statement(why should you want? but you can’t)
Since dynamic T-sql statement is not supported in side a function so we do not need exec. Please correct me if i am wrong.
Hello,
Sorry If I wasn’t very clear.
I had in mind that in generally you use a function to calculate something small that you need to repeat in your code. For example: a particular value based on very clear rules (like constants etc.). And what I wanted to said is that I believe there is no need for dynamic sql for such things.
Of course the SQL will loose from its beauty if “dynamic sql” does not exists.
I hope now is clear
Stored procedures are Pre-Compiled and Stored. When we First execute a stored procedure its plan is cached and used in subsequent executions(until we Flush the cache using FREEPROCCACHE or clearing Cache Buffers).
Bu, where as a Function is just an encapsualtion of SQL statemets (not DDL,Control and Windowing functions ex:(ROW_NUMBER() ) so every time a function executes it causes re-compilation.
According to MSDN, both Stored Procedures and Functions are precompiled: https://docs.microsoft.com/en-us/previous-versions/6z6w48f5(v=vs.140)
In my development work we tend to prefer parameterized SQL select/update/delete statements within code instead of stored procedures. This keeps the logic centralized within the application rather than spread it out across both application code and stored procedures. Isolate the DB usage in a data access layer/classes and things are simple to understand and modify.
One reason we would consider using stored procedure is if the DB usage scenario generates a lot of queries. The additional time and network traffic overhead required by lots of DB queries per request can potentially be mitigated by moving logic to store procedures.
Oh total fail!
functions can only GET the state of “Data”
stored proc can SET and GET the state of “Data”
Data = database (tables ,views etc)
Chris V, unless I’ve misunderstood what you’re saying, what you’re doing represents one of the cardinal sins of application development. You separate the data and application layer to ensure consistency, reusability, and separation of logic from data manipulation. You’re also at much higher risk of SQL injection attacks from embedding SQL statements in your code. Finally, stored procedures make it easier for you to have several different applications that re-use the same data manipulation.
To answer Pinal Dave’s question 3, we generally use functions to encapsulate functionality or calculations that might give a single output. We use stored procedures to create, extract and manipulate data. So whilst a function might be used within a stored procedure (for example, to calculate a value given some inputs), we would most likely use it within a stored procedure that selected some larger dataset, or set some data value to the result of the function.
Functions can be used within the context of another SQL statement, so you can use a function as one of the columns in a SELECT statement. You can’t do that with a stored proc.
There may be many reasons:
(i) Security: We already aware about the depth of DB security comparison with file-level.
(ii) Increases the performance: By using stored procedure we can increase the db performance or it seed-ups the db job.
If you need a scalar valued/table valued result, which is not time consuming then u can prefer for function.
Function won’t go through the stage of compile and creating execution plan (so it can create performance impacts).
Stored Procedure : By the name its explained that all procedures are stored in database are compiled and it had an execution plan.
This improves the performance.
SPs can provide multiple record sets
Function can only fetch one table valued result.
SPs can execute time consuming functions(print, exec).
But Function cann’t do that as it doesn’t have any execution plan.
SPs can use transaction
But Function cann’t.
Stored procedures are designed to return its output to the application. A UDF returns table variables, while a SPROC can’t return a table variable although it can create a table. Another significant difference between them is that UDFs can’t change the server environment or your operating system environment, while a SPROC can. Operationally, when T-SQL encounters an error the function stops, while T-SQL will ignore an error in a SPROC and proceed to the next statement in your code (provided you’ve included error handling support). Functions can be executed from select statement.. UDF will contain only one select statement for projection. Functions are used to send outputs to queries.
1. We can not use stored procedure in select statement but we can use user defined functions in select statement. In such case where you want to use result of specific block in query you can use user defined functions.
2. I think stored procedures takes less space to get stored in database since its in pre-compiled format. But UDF’s takes little bit more space as compared to stored procedure. Since its get compiled every time it gets called.
3. You can not perform DML operations in UDF’s. But same thing can be done using Stored procedures.
Agree with Djay that UDF concept should stay at “GET data” and let SP to “GET and SET data”. However there are still a lot of limitations of UDF which prevent you to use UDF in “GET data” scenario. UDF might be used in many more scenarios if they would support things like MAXRECURSION option at CTE, RAISERROR function usage, support READ-WRITE user defined table types, support more non-deterministic function than getdate() (e.g. newid()) etc.
1. When we need some data to be calculated and taken as field in other query, we have to take function.
2. DML queries can not be performed in function so we have use stored procedure.
Below are some scenarion where we can say which one is most suitable:
While user defined functions can offer great convenience, they can also sometimes hurt performance. This problem with them is that they use row-by-row processing, similar to how a cursor works, instead of working as a set-based operation. So if the result set of your query, which is using a user defined function, is very small, then the performance hit will be small. But if the result set is large, then performance could very well become a problem. Generally speaking, if you are using a user defined function, you will want to avoid using them with large result sets. Instead, use a well-written stored procedure instead.
I think instead of think what a procedure can do, we should think what function can do that a procedure can’t.
And i think the most important thing a function can do is to be processed inside a query(a procedure can’t), example:
——————————
select top 50
cod,
dbo.calculate_tax(total, region) as total_taxed
from
techstore_receipt
——————————
And you should always consider the performance avoiding using in large result sets
Conclusion: You should only use functions in a case like this and with small result sets, otherwise use procedures
Hi Pinal,
After seraching a lot I thought of asking you question under this article..Well I would really appreciate if you could help me out with this query….
I am trying to count the occurrences of values across the row for a single record…and then have to compare those number of occurrences.If they are equal then I am o.k with that record if it is not equal then I have to pick that record up.
Below is my sample date..
RecId Col1 Col2 Col3 Col4 Col5………………………………………Col20
1 x x y y
2 y x x y
3 a x x y y
4 y y x x
5 a x x y a
hence i have to find for record 1 how many times x repeating and so y to check whether they are equally occuring.
for eg Recid 1 has x in col1 and col2 and so y is also there for same number of time.and Recid 5 has two time x but one time y so I have to pick that record.
In the above table I have 20 columns 1 to 20 and the value of any column could be either a, x, or y…no particular format of having specifc value.
Could you help me with this please how to obtain the desired result…
Counting Column wise is possible but how to count row wise??
Your prompt reply would be highly appreciated…
Thanks &Regards,
Sana.
@Sana,
CREATE TABLE test
( col1 CHAR(1),
col2 CHAR(1),
col3 CHAR(1),
col4 CHAR(1),
col5 CHAR(1),
col6 CHAR(1),
col7 CHAR(1),
col8 CHAR(1),
col9 CHAR(1),
col10 CHAR(1),
colId int
)
INSERT INTO test
VALUES (‘x’,’x’,’y’,’y’,’x’,’y’,’a’,’b’,’c’,’x’,1),
(‘z’,’t’,’y’,’x’,’x’,’y’,’a’,’y’,’c’,’x’,2),
(‘y’,’y’,’y’,’x’,’x’,’x’,’a’,’c’,’c’,’b’,3),
(‘c’,’y’,’y’,’c’,’c’,’y’,’a’,’x’,’x’,’x’,4)
;WITH XY_CTE (Colid, XYCount, Col)
AS
— Define the CTE query.
(
SELECT colid, Count(Xcol) cnt, xcol
FROM
(SELECT Colid, Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10
FROM test) p
UNPIVOT
(
XCol FOR x IN
( Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10)
)AS unpvtX
where xcol in (‘x’, ‘y’)
Group by colid, xcol
)
select * from XY_CTE T1, XY_CTE T2
Where t1.colid=t2.colid
and t1.xycountt2.Xycount
hey pinal what is your answer? which of these answers is correct?
@Khurram,
Thanks for your reply.Yea this is one of doing it..and I have also used Case and derived table….But the way you showed is quick…
Thanks again..
Regards,
Sana
We can’t call store procedure in user define function…………….. Why?
plz reply.
I want to return amount in words which now returning numeric value.Is that i have to create function if yes then tell me use how to use that function on that particular column on storeprocedure.
Write a stored procedure whose input is a player last name and whose outputs are era and average. Test it. For null values, output NA (first choice) or 0 (second choice). Your tests should make sure all the desired functionality is covered. The output here should be like : robert has an era of 32 and an average of 270
if era or average is null it should display robert has an era of 0 and an average of 270 (era is null)
last name is in tABLE emp
era and avg are in table info