• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

Stored Procedures

What is the importance of stored procedures?
Is it necessary to use stored procedures in every project? why?
0
Whing Dela Cruz
Asked:
Whing Dela Cruz
  • 2
  • 2
1 Solution
 
momi_sabagCommented:
stored procedures provide 2 main advatages:
1) encapsulation - if you have some logic to perform in the database, the application don't know about this logic. The stored procedure provides an api, and you can later change the stored procedure as much as you want without changing the application at all (as long as the api dosn't change)
2) it has better performance - if you need to perform multiple statements, you only perform one call to the database, and all the work is done there, and you receive a response. this way you minimize the amount of database calls and network traffic that your application needs

it is not a must to use stored procedures in every application, but it is recommended
0
 
Whing Dela CruzAuthor Commented:
Can  you give me example on how to do it? or any advice to start it?
Thanks!!
0
 
momi_sabagCommented:
you can find tones of example on the the internet
just google for
sql server stored procedures examples
calling sql server stored procedures from vb
0
 
Whing Dela CruzAuthor Commented:
Okey thanks!
More power to you!
0
 
Chris LuttrellSenior Database ArchitectCommented:
This is just an example to get customer info from the customer table by passing in the id.  They can be much more complex if needed.  As for how to start, just take the queries you have and call and put the conditions that change like CustId as the parameters up top and the variable into the query.
-- Create the SP
CREATE Procedure spCustomerInfo
@CustId int
as
Select CompanyName, LastName, FirstName, OpenBalance, CreditLimit
From Customer
Where CustId = @CustId
GO
 
-- Use it
exec spCustomerInfo @CustID = 100
-- or this will work also
exec spCustomerInfo 100

Open in new window

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now