I have to strongly disagree with the two other respondants. The real answer
is that it totally depends. To say that stored procedures are "far more
secure" is extremely misleading. By simply using parameterized values in
your dynamic SQL you've achieved high security. Conversly, by using execute
in your stored procedure it's not at all secure.
As far as performance, it really depends on what you are doing. That SQL
Server caches queries is a very misleading generalization. Complexe queries
are often uncacheable. Additionally, client-side (ASP.Net) caching can
belittle this minor advantage. What's worse, the most efficient search
queries are typically written in one form or another of dynamic sql
(otherwise SQL Server can't take advantage of indexes).
Personally I'm a fan of stored procedures (big time), but blanket statements
are dangerous. Saying that stored procedures are secure dangerously risks
taking the responsability away from the developer. Additionally, different
solutions are better suited for different scenarios. As such, only someone
who doesn't subscribe to blanket statements can make the right decision.
Let's look at a simplified example:
create procedure SearchProperty AS
@ListingType INT --pass 0 to search for all listing types
select listingId, listingName from Property
where (@listingType= 0 OR ListingType = @ListingType)
AND Status = 1
In the above example we need to allow 0 to be passed in to mean any listing.
This will likely result in SQL Server being unable to use an index on the
ListingType column, thus resulting in less that optimal performance. To get
ideal performance, we'd need to if the code:
if @ListingType = 0 BEGIN
select listingId, listingName from Property
WHERE Status = 1
END ELSE BEGIN
select listingId, listingName from Property
WHERE ListingType = @ListingType AND Status = 1
END
Now you are getting somewhere...except that it'll get impossible to maintain
with the addition of just a few more search parameters (and query plan
caching will increasingly become less efficient).
We could use execute:
declare
@sql varchar(1024)
set
@sql = 'select listingId, listingName from Property WHERE Status = 1'
if @ListingType = 0 BEGIN
SET
@sql =
@sql + ' AND ListingType = ' + @ListingTypeId
END
exec(
@sql)
Looky that, a stored procedure and a HUGE potential for SQL
injection...egads! (yes, the above sproc won't actually work but if
@ListingTypeID was a varchar (say comma-separated values) it would).
This simple, and common, example not only illustrates that stored procedures
aren't necessarily faster and just vulnerable, but that they can also be
harder to maintain.
In short, the correct answer is "it depends" and hopefully as you learn more
you'll learn to use the right solution at the right time
Karl
--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)