[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Stored Procedures

Posted on 2009-03-29
5
Medium Priority
?
242 Views
Last Modified: 2013-11-25
What is the importance of stored procedures?
Is it necessary to use stored procedures in every project? why?
0
Comment
Question by:Whing Dela Cruz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24011732
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
 

Author Comment

by:Whing Dela Cruz
ID: 24011746
Can  you give me example on how to do it? or any advice to start it?
Thanks!!
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 2000 total points
ID: 24011749
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
 

Author Closing Comment

by:Whing Dela Cruz
ID: 31563989
Okey thanks!
More power to you!
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24011758
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question