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
Mr or Mrs. 500
Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)

Group: General Forum Members
Points: 513 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 Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97927 Visits: 33014
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
Mr or Mrs. 500
Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)

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

Razi, M.
http://questivity.com/it-training.html
ALZDBA
ALZDBA
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29615 Visits: 8986
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 Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97927 Visits: 33014
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
Dave Poole
Dave Poole
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16676 Visits: 3403
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
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