Solved

Stored Procedures

Posted on 2009-03-29
5
231 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
  • 2
  • 2
5 Comments
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
Okey thanks!
More power to you!
0
 
LVL 26

Expert Comment

by:Chris Luttrell
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now