SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored Procedure vs Parameterized queries


Stored Procedure vs Parameterized queries

Author
Message
ramoha
ramoha
SSC Veteran
SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)

Group: General Forum Members
Points: 299 Visits: 193
I have a developer who keeps praising the Parameterized Queries instead of stored procedures. Can you anyone put some light on which one is the best way? I personally feel that stored procs are the best and most efficient way of retrieving or updating data.

Razi, M.
http://questivity.com/it-training.html
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39203 Visits: 32616
That's one ugly debate you're entering into. Short answer is, parameterized queries function the same way as stored procedures. SQL Server will be able to reuse execution plans because it will recognized the parameters as such. So from the performance point of view, there's no real difference.

The difference comes from code maintenance, maintainability, and performance tuning. In order to tune a single line of code requires a complete deployment of that code. If you're in a tiny little shop with a couple of developers and you're going straight to production with every successful build, who cares. If you're in a larger shop or dealing with more sensitive applications, you'll need to go through QA, acceptance testing, financial testing, etc., for every change to a line of code. Where as, with proper configuration and testing, you can adjust the internals of a stored procedure without changing it's interface and deploy it without affecting the client or app code. Huge difference. I'd go for those parts of the argument rather than strictly over performance.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
ramoha
ramoha
SSC Veteran
SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)

Group: General Forum Members
Points: 299 Visits: 193
Thanks for the reply!!!

Razi, M.
http://questivity.com/it-training.html
ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12029 Visits: 8918
my addition....

Indeed, for plan reuse, the line is getting very thin.

regarding security, the gap has not been closed.

- "curse and blessings of dynamic sql" is still valid; I haven't got its url at this moment, but google for it.

- You will want to prefer sprocs because of its documentation value, security, tunability (by the dba if needed), ...

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39203 Visits: 32616
True, I forgot about that. Thanks.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
David.Poole
David.Poole
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7447 Visits: 3279
I can't stress the maintenance issue enough.
Let us suppose you hire a good DBA. By locking SQL in a code release you are hamstringing your DBA; you are not going to get the best out of them unless they write the code.

The principle of DATA IS SECURE is a real bug bear as well. It is surprising what should actually be considered secure and what is not. You may think that a name and address is on the electoral role therefore why bother securing it? Well if you know that name and address is for a manager in the FTSE Top 100 it goes from being a £0.05 name to a £5.00 name.

If the name is on the MI6 employee register then it absolutely should be secure. Dynamic SQL implies direct table access implies there is a login that can smash and grab data en-masse.

LinkedIn Profile

Newbie on www.simple-talk.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search