Difference Between Stored Procedure and User Defined Function in SQL Server

-
Sr.No.User Defined FunctionStored Procedure
1 Function must return a value.Stored Procedure may or not return values.
2Will allow only Select statements, it will not allow us to use DML statements.Can have select statements as well as DML statements such as insert, update, delete and so on
3 It will allow only input parameters, doesn't support output parameters.It can have both input and output parameters.
4It will not allow us to use try-catch blocks.For exception handling we can use try catch blocks.
5Transactions are not allowed within functions.Can use transactions within Stored Procedures.
6We can use only table variables, it will not allow using temporary tables.Can use both table variables as well as temporary table in it.
7Stored Procedures can't be called from a function.Stored Procedures can call functions.
8 Functions can be called from a select statement.Procedures can't be called from Select/Where/Having and so on statements. Execute/Exec statement can be used to call/execute Stored Procedure.
9A UDF can be used in join clause as a result set.Procedures can't be used in Join clause

Comments

Post a Comment

Popular posts from this blog

List out different return types of a controller action method?

-
There are total nine return types we can use to return results from controller to view.  The base type of all these result types is ActionResult. ViewResult (View) : This return type is used to return a webpage from an action method. PartialviewResult (Partialview) : This return type is used to send a part of a view which will be rendered in another view. RedirectResult (Redirect) : This return type is used to redirect to any other controller and action method depending on the URL. RedirectToRouteResult (RedirectToAction, RedirectToRoute):  This return type is used when we want to redirect to any other action method. ContentResult (Content) : This return type is used to return HTTP content type like text/plain as the result of the action. jsonResult (json):  This return type is used when we want to return a JSON message. javascriptResult (javascript):  This return type is used to return JavaScript code that will run in browser. FileResult (File): ...
Read more

3-6 yr Experience Interview Questions in .Net Technologies

-
3-6 yr Experience Interview Questions in .Net Technologies Hai Friends, Below I am posting the questions and answers for the short questions for 3-6 years experience guys. These questions will be helpful for those who are either preparing for the interview or attending the interviews. This will also be helpful for the last minute preparation in quickest way. If anyone has better answers, please reply to this post and I will include the better answer to the post as it will be helpful for all of us. CLR and C# 1. Types of Authentication IIS. A. Authentication is the process which helps web server(IIS) to check and confirm the identity of the client who request to access the website. Types of Authentication: a. Http Authentication: Basic Authentication, Digest Authentication b. Integrated Windows Authentication: NTLM(Network Lan Manager), Kerberos c. Client Certificates Access d. Anonymous and UnAuthenticated Access e. Logon-Redirection based: Form Authentication(IIS 7.0) ...
Read more

Explain what is the difference between View and Partial View?

-
View: It contains the layout page. Before any view is rendered, viewstart page is rendered. View might have markup tags like body, html, head, title, meta etc. View is not lightweight as compare to Partial View. Partial View: It does not contain the layout page. Partial view does not verify for a viewstart.cshtml.We cannot put common code for a partial view within the viewStart.cshtml.page. Partial view is designed specially to render within the view and just because of that it does not consist any mark up. We can pass a regular view to the RenderPartial method.
Read more