Why should select statement be used in stored procedure insteadi of selecting from tables or views

Question

0.00/5 (No votes)

See more:

I've seen people using select statements in stored procedures instead of selecting from views/tables.
Example. Suppose there's a view with name xview.
Instead of witting "select * from xview",
They do this:
Create procedure spSelect
As
Select * from xview
End

Then they execute spSelect to return the same result.

Question
1. What are the pros and cons of using either in such ways
2. What instances should selecting from views be used and vice versa.

What I have tried:

I haven't tried that yet but I just don't understand why they do that

Posted 19-Oct-16 3:52am

kwaku Emma

Updated 19-Oct-16 17:37pm

Add a Solution

3 solutions

Solution 1

In older versions of SQL there was a benefit to 'some' select statements being included in stored procedures because these were 'compiled' to give better performance.

I've never seen one quite so simple, tho. I doubt even in older versions that this would help.

In newer versions of SQL, all queries are 'compiled' for better performance, regardless of whether or not they are in a stored procedure or not. This practice is almost entirely pointless.

I say 'almost' quite deliberately. If this isn't done out of pure habit then there are (or were) probably some internal common practices, or a custom query executor that requires (or required) stored procedures. The reasons behind these possibilities could be few or many.

The only other possibility I can think of is that much of the business logic is contained in the SQL Stored Procedures. If this is the case then it is very worth while.

Example:
Most clients just want everything from a table but some must have and 'columnname=value' excluded. Depending on how complex that select is then you can include that logic in the SP without having the change the software. VERY useful when you sell a product like that

Posted 19-Oct-16 4:52am

Andy Lanng

Solution 2

In addition to what Andy says above, it helps with security and maintenance to keep everything through a single point of access. It's better to only ever allow code to be accessed via SPs rather than having both SPs and Views directly accessed and having the complications that come with that.

Posted 19-Oct-16 5:26am

F-ES Sitecore

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2016-10-19T17:37:00

To add to previous posts. I also can't find very good reasons to use procedures as a design principle. There should be real reasons behind.

Especially procedures handling SELECT statement often become clumsy. Think about a situation where you expect to receive a set of rows based on some criteria. You're tied to the parameters the procedure has and if you want to add ability to use a new criteria, you need to change the parameters along with the query inside the procedure.

In overall changes in the database structure causes changes on the client. In many cases the procedures add an extra layer that needs to be changed thus causing extra work.

What comes to security, yes procedures can be used to prevent the caller from seeing the underlying tables. However so can a view. This is one thing what the view is designed for.

If we think outside selecting, then procedures may become handy. For example:
- if you need to change several tables or rows during the same call
- if you need to include business logic into change operations, procedure is a good candidate for that
- if you need to prevent the client side for making modifications directly to tables. As said a view can also hide the table but more complex views may not be updatable so in such case you'd need t give access to the table itself or create another view for changes.

One important thing that affects the choice for procedure is transaction handling. If the transactions are started and ended on the database server, then procedures may be mandatory. Especially if multiple operations are included. If the transaction is handled on the client side then you can choose more freely.