Re: Alternative to Dynamic SQL?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Why would you deny a specific user access to the DB? Do users really
connect to your database and run queries??? This is a very poor
argument. If security is that much of a concern, shutdown your sql
servers.

In the application, each database is suppose to have an application
side DAL where all applications depending on that database go through
that specific DAL. Stored procedures could be considered a "DAL" but
have you tried maintaining 1000s of SPs? Especially when theres
obsolete ones? Also, when there are modifications, how do you refactor
or do you even get intellisense? Do you just create another SP?

The idea that the application should just ask for data from the
database and let the database worry about retrieving it will have some
serious performance impact on the application. For an example, like
pagination and search interfaces where using static SQL or stored
procedures is just "cowboy coder" insane.


On May 31, 10:06 pm, "Michael Coles" <admin.AT.geocodenet.DOT.com>
wrote:
"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)!"

Try denying your user access to the table and then see how well it works in
the application layer...

--

========
Michael Coles
"Pro T-SQL 2008 Programmer's Guide"http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X

<sqlg...@xxxxxxxx> wrote in message

news:4d1d033b-27fd-450b-a267-7a0ea09d6365@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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

  • Fixed
    ... I also found this article that gives the proper way to move system dbs in sql 2008: ... The model exists where the master states it exists. ... This is the error log prior to detaching the model database. ...
    (microsoft.public.sqlserver.server)
  • Re: CREATE AGGREGATE failed because type Concatenate does not conform to UDAGG specification due to
    ... Go to the Database tab and click on the browse button next to the connection string. ... In the New Database Reference dialog, enter the details for the database where you want to deploy the assembly and create the user defined aggregate. ... I'm trying to do some CLR integration with sql server 2005. ...
    (microsoft.public.sqlserver.programming)
  • CREATE AGGREGATE failed because type Concatenate does not conform to UDAGG specification due to meth
    ... Now register the assembly and the aggregate in the SQL Server database you want ... I'm trying to do some CLR integration with sql server 2005. ...
    (microsoft.public.sqlserver.programming)
  • Re: dbdebunk Quote of Week comment
    ... > a lot of really bad SQL programmers. ... But SQL does not have a pointer data type or the ... > being told to design a database. ... But why is little Cindy Lou Who employee ...
    (comp.databases.theory)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)