Re: Alternative to Dynamic SQL?

<sqlguru@xxxxxxxx> wrote in message news:df3370e7-68b1-4e06-b23d-671d007a2cf6@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I don't know what kind of IT company you're working for but ANY of the
enterprise companies, SQL servers are fully locked down. That means
that a user cannot just directly connect and start modifying tables.
We have a single user per database (sometimes per application). This
isn't an access database, users shouldn't connect and start modifying
data without going through a front-end.

LOL. Fully locked down, eh? You use SQL Server logins for security as opposed to Integrated Security? I'd love to see how you audit changes to your databases with a single user per database, or a single user per application. Or do you create a separate database for each of your end users? Truly amazing.

Don't be a mindless drone, SQL Server is not an access database, there
is no such thing as "power users" except in 1980s IT companies with
very old methodologies. Users do not connect to a SQL server, period.
All modifications go to the DBAs as scripts.

No one ever establishes a connection to your SQL Server, period. One wonders how in the world data is retrieved from your SQL Server, period.

With inline queries, each query is usually tied to a method. This
gives it much more "meaning" and what the query does and how it
relates to the method/application. With stored procedures, you got
5000 stored procedure, the only clue you got to as what each SP does
is maybe the name.

I call shenanigans! You can easily tie a stored procedure "to a method". As you said previously your "data dictionary" provides additional "meaning"; assuming you created a proper data dictionary you have a "clue".

What I meant was that some companies let the DBA do all the database
side processes and the developers do the application side processes.

You should maybe concentrate more on expressing "what you mean" as opposed to shooting off at the keyboard with some ridiculous rants and half-concocted statements.

The problem with this is that sometimes, the two groups are not in
sync for specialized problems. For an example, if Celko was the
DBA....and Tony was the developer and Tony wanted a pagination query,
Celko would probably return the whole table to the front-end for
pagination. The better way would be to do a database side pagination
query.

Celko pulling 100 GB of data across the wire to paginate 20 lines at a time client-side has no bearing on the discussion at hand. You can use a stored procedure to paginate database-side as well, so your point is moot.

OR when a complex search interface is being made, the DBA would
probably create a 1000-parameter stored procedure instead of an inline
query.

That's another Celko-ism and has no bearing on the question at hand. If you are creating a complex search interface that requires "1000-parameters", you can easily pass them as XML, delimited string, or even using a table-valued parameter to a stored procedure. I suppose you recommend dynamically generating a 1,000 parameter query string and passing it over the wire, in addition to the 1,000 parameter values, every time? Does that sound about right?

--

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

.