Use of Stored Procedures and Views

All Replies

• Wednesday, November 10, 2010 2:27 PM

   

   
  

  0

  Sign In to Vote

  >>I don't know where (in which cases) views and procedures are used?

   

  *****************

  VIEW

   

  A view is a “virtual” table consisting of a SELECT statement, by means of “virtual”

  I mean no physical data has been stored by the view -- only the definition of the view is stored inside the database; unless you materialize the view by putting an index on it.

   

   

  1)     By definition you can not pass parameters to the view

  2)     NO DML operations (e.g. INSERT, UPDATE, and DELETE) are allowed inside the view; ONLY SELECT statements.  

   

  Most of the time, view encapsulates complex joins so it can be reusable in the queries or stored procedures. It can also provide level of isolation and security by hiding sensitive columns from the underlying tables.

   

   

  Stored Procedure

   

  A stored procedure is a group of Transact-SQL statements compiled into a single execution plan or in other words saved collection of Transact-SQL statements.

   

   

   

  Here is a good summary from SQL MVP Hugo Kornelis (was posted in an internet newsgroup few years ago) 

   

  *********

  A stored procedure:

  * accepts parameters

  * can NOT be used as building block in a larger query

  * can contain several statements, loops, IF ELSE, etc.

  * can perform modifications to one or several tables

  * can NOT be used as the target of an INSERT, UPDATE or DELETE

  statement.

   

   

  A view:

  * does NOT accept parameters

  * can be used as building block in a larger query

  * can contain only one single SELECT query

  * can NOT perform modifications to any table

  * but can (sometimes) be used as the target of an INSERT, UPDATE or

  DELETE statement.

   ********************/

   

  • Reply
  • Quote

   
• Wednesday, November 10, 2010 2:31 PM

   

   
  

  0

  Sign In to Vote

  Hello,

  Here is my personal opinion:
  
  We usually use Views when you want to define the information you want in one table, for example if you have 3 tables with some relation between then and then you would like to retrieve all the information together, just showing the data you really need from the 3 tables joined, then you configure a view to get that information together.
  
  Stored Procedures, are used to perform may task together. Usually you use SP when there is a process that comes repetitive and you would like to call the same procedure from your application to perform, in some cases, an static task. Stored Procedures receive parameters used validate, insert data, update, delete, etc.
  
  Here 2 post about how to Increase SQL Server Stored Procedures Performace:
  
  http://articles.techrepublic.com.com/5100-10878_11-1045447.html
  
  http://www.mssqlcity.com/Tips/stored_procedures_optimization.htm

  
  Here some definitions about what-is and what to use in some cases (Views, Stored Procedures or User-defined functions)
  
  http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html

   

  
  Hope this helps.

   

  ---

  Willy Taveras.-
  
  http://itlearn.net

  • Marked As Answer by stylish16 Wednesday, November 10, 2010 2:43 PM
  •  
  • Reply
  • Quote

   
• Wednesday, November 10, 2010 2:46 PM

   

   
  

  0

  Sign In to Vote

  Hello,

  Here is my personal opinion:
  
  We usually use Views when you want to define the information you want in one table, for example if you have 3 tables with some relation between then and then you would like to retrieve all the information together, just showing the data you really need from the 3 tables joined, then you configure a view to get that information together.
  
  Stored Procedures, are used to perform may task together. Usually you use SP when there is a process that comes repetitive and you would like to call the same procedure from your application to perform, in some cases, an static task. Stored Procedures receive parameters used validate, insert data, update, delete, etc.
  
  Here 2 post about how to Increase SQL Server Stored Procedures Performace:
  
  http://articles.techrepublic.com.com/5100-10878_11-1045447.html
  
  http://www.mssqlcity.com/Tips/stored_procedures_optimization.htm

  
  Here some definitions about what-is and what to use in some cases (Views, Stored Procedures or User-defined functions)
  
  http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html

   

  
  Hope this helps.

   

  ---

  Willy Taveras.-
  
  http://itlearn.net

  Thanks For the reply Willi.                                                                                               Can I use View for Login Validation Purpose ?                                                                         query that i have used consists of Join
  
  • Reply
  • Quote

   
• Wednesday, November 10, 2010 2:51 PM

   

   
  

  0

  Sign In to Vote

  Thanks For the reply Willi.                                                                                               Can I use View for Login Validation Purpose ?                                                                         query that i have used consists of Join
  

  
  I usually use a Stored Procedure that receives UserName and Password as parameters, then inside I check all the user information and manage the response code with a variable. For example, if user doesn't exists then the response code is 1 (for example!), everything success response code is 0, user already logged (if need that validation) then use response code  = 2.
  
  It's all up to you, depends on what you want to validate or how are you going to validate the user login validation.
  
  I prefer using Stored Procedures to do that.
  

  ---

  Willy Taveras.-
  
  http://itlearn.net

  • Marked As Answer by stylish16 Wednesday, November 10, 2010 3:05 PM
  •  
  • Reply
  • Quote

   
• Wednesday, November 10, 2010 3:05 PM

   

   
  

  0

  Sign In to Vote

  Thanks For the reply Willi.                                                                                               Can I use View for Login Validation Purpose ?                                                                         query that i have used consists of Join
  

  
  I usually use a Stored Procedure that receives UserName and Password as parameters, then inside I check all the user information and manage the response code with a variable. For example, if user doesn't exists then the response code is 1 (for example!), everything success response code is 0, user already logged (if need that validation) then use response code  = 2.
  
  It's all up to you, depends on what you want to validate or how are you going to validate the user login validation.
  
  I prefer using Stored Procedures to do that.
  

  ---

  Willy Taveras.-
  
  http://itlearn.net

  Thanks again Willy.
  
  • Reply
  • Quote