Parameterized query Vs stored procedure

Question

Hi, Is there any difference between parameterized query and stored procedure query??

Suthish Nair · Answer

Stored Procedure stored at database level. We can say its a medium for applications to access the Data Base related informations. Parameterized query is an T-SQL query that accepts parameters as same as from stored procedure or the DML statement you having. To avoid SQL Injection Attacks we need to use Parameterized queries. Even we can use Parameterized queries in Stored Procedure level. One of below link explains that. Parameterized Query and SQL Injection Attacks More If you want more debate More I know its not to share junk of links, but above having different explanation and information.

Suthish Nair · Answer

The first link says when to use Parameterized Queries in VS. Why we need to use SPs. 1. Flexible 2. Faster Performance because SPs are alrerady complied. 3. You can avoid updating the code behind sql statements every time. 4. We can avoid rebuilding of dlls every time, if you using SPs. 5. The important part is N-Tier Archie. Its not good to write your sql statements in DAL/BAL. Keep it seperate.

Savita Joshi · Answer

Thanks Suthish and Andrew.. I want to know when do we need to use Parameterized query and when to use stored procedure? How do we decide on this?

Andrew Fenster · Answer

Stored procedures may or may not be vulnerable to SQL injection. It depends how you write the stored proc. I can't think of any way to write a parameterized query that's vulnerable to SQL injection.

Andrew Fenster · Answer

Well, obviously the way you call a stored proc and the way you make a parameterized query are somewhat different, but that's probably not the question. If you're asking about how the database deals with the call, and which is faster, it depends on the database. SQL Server and DB2 are about the same in the way they react, so I'll describe that. Whenever you make a call, the database has to do a few things. It has to validate your call to make sure all the tables, columns, functions, etc. in your call really exist. It checks your syntax. It builds a plan to execute and then it executes it. When you create a stored procedure, the database does a lot of this work in advance. It does all the validation and builds a plan. When you call the stored procedure, all it has to do is plug in your input parameters and run. When you make a paremterized query, the database has to do everything right then: validate, check syntax, build the plan, etc. So the first time you make a parameterized call, it's much slower than calling a stored proc. HOWEVER, both DB2 and SQL Server then store the plan for a little while. So if you make the exact same paremeterized query, but with different input parameters, it runs much faster the second time. It runs at the same speed as a stored procedure. So if you're going to make the same call 100 times in row, it doesn't make much difference whether you do it with a stored procedure or parameterized query, at least, not as far as performance goes.