Re: Alternative to Dynamic SQL?

The argument of SQL injection is invalid now because using command
parameters is a standard. I actually read your article on dynamic
search querying using stored procedure and found it invalid. It's a
huge maintenance hassel because search requirements often change and
is prone to "runtime" issues if you fail to test properly. You also
use sp_executesql to execute a dynamically built SQL inside a stored
procedure (you might as well do it in the application layer because it
gets cached either way)!


On May 30, 5:55 pm, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
 (sqlg...@xxxxxxxx) writes:
The best solution is to use dynamic SQL. There isn't any problems with
dynamic SQL, modern database engines actually cache dynamic sql and
benchmarks show that there isn't any real differences between SP's and
dynamic SQL.

Oh, if life was that easy! There are plenty of possibilities to wreck a
server with dynamic SQL if you do it wrong. Used right and with care, it
is a tremendous asset.

--
Erland Sommarskog, SQL Server MVP, esq...@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.

Relevant Pages

  • Re: Views vs Stored Procedures, whats the difference?
    ... I hope you are not suggesting you embed SQL queries into the application? ... A stored procedure logic will be exactly as fast as the algorithm you ... I understant that SQL Server supports hints. ... implementations (nestedloop, merge, hash, ..) on decent sized tables, then ...
    (comp.databases.ms-sqlserver)
  • Re: Stored Procedures - Patterns and Practices
    ... >published the reasoning behind its opinions. ... I disagree that the debate in SQL Server related discussion forums ... If the natural key is long or spans too many ... I want to call a stored procedure that adds a customer ...
    (microsoft.public.sqlserver.programming)
  • Re: Issue with retrieving large data over web using Stored Procedu
    ... how do I go about analyzing a stored procedure with selecting ... Is there any tool in the SQL Profiler that analyze each Trace? ... "Active Server Pages error 'ASP 0113' ... This email account is my spam trap ...
    (microsoft.public.inetserver.asp.db)
  • Re: Problem using SP as record source with Access2002 & SQL Server 7
    ... I've installed a brand new copy of SQL Server 2000 with the original ... I've created the folowing stored procedure for the resync ... Resync Command: ALL_CUSTOMERS_RESYNC? ... I need to upgrade to a newer version of Microsoft SQL ...
    (microsoft.public.access.adp.sqlserver)
  • Re: slowing/halting stored procedure from ado.net
    ... but at the time when calling from the application ... calling the same stored procedure from SQL Management ... Studio goes without any slowdown, ... There was/is no big job running on the sql server, ...
    (microsoft.public.dotnet.framework.adonet)
Loading