Stored Procedure vs Parameterized queries

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Stored Procedure vs Parameterized queries

Stored Procedure vs Parameterized queries

Rate Topic

Display Mode

Topic Options

Author

Message

Razi, M

Posted Tuesday, January 22, 2008 11:43 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:37 PM
Points: 214, Visits: 166

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

Post #446034

Grant Fritchey

Posted Tuesday, January 22, 2008 12:55 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 13,383, Visits: 25,189

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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #446076

Razi, M

Posted Tuesday, January 22, 2008 1:06 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:37 PM
Points: 214, Visits: 166

Thanks for the reply!!!

Razi, M.
http://questivity.com/it-training.html

Post #446080

ALZDBA

Posted Tuesday, January 22, 2008 1:14 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:17 AM
Points: 6,862, Visits: 8,049

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

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't 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" BigGrin

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #446089

Grant Fritchey

Posted Tuesday, January 22, 2008 1:29 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 13,383, Visits: 25,189

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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #446098

David.Poole

Posted Monday, September 06, 2010 10:32 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:46 AM
Points: 2,750, Visits: 1,410

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

Post #981187

« Prev Topic | Next Topic »

Permissions