Thoughts on the Wisdom of this approach?

Question

Thoughts on the Wisdom of this approach?

john-902052

SSCommitted

Points: 1578
More actions
October 4, 2010 at 11:33 pm

#228945

All,
This should be an easy one. I have been thinking about writing a query something like:
-- =============================================
-- Author:John D. Sanders
-- Create date: 10/4/2010
-- Description:Get all records from a DB of a Table
-- =============================================
CREATE PROCEDURE [dbo].[udp_GetRecordsFromTableByID]
@DB_NAME nvarchar(max),
@TABLE_NAME nvarchar(max),
@WHERE_CLAUSE nvarchar(max)
AS
BEGIN
declare @cmd nvarchar(max)
SET NOCOUNT ON;
SET @cmd = N'SELECT * FROM ' + @DB_NAME + '.dbo.' + @TABLE_NAME + ' ' + @WHERE_CLAUSE
EXEC sp_executesql @cmd
END
My thinking is that I won't have to write all of the sps that just get a record by the identity column and can just do that in my DAL layer. But am I cutting my foot off, viz RDBMS optimization of the query or something else???
I would really appreciate your thoughts on this.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Two words: SQL Injection

Seriously, if you're thinking of doing this don't bother with the stored proc and generate the ad-hoc SQL from the front end.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

john-902052 SSCommitted Points: 1578 More actions · Answer 2

Everything right now is in SPs I just have a bunch of little ones like

select * from Table where Key = @key

was looking for a better (safe) way of doing it so I don't have to maintain so many SPs. But if that is not good then I will just not do it.

BTW this is Winforms.

Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

I wouldn't advise you to do this, for the following reasons:

(1) SQL Injection, as already mentioned by Gail.

(2) Permissions. If all your access to the tables is via stored procedures, then you only need to grant users excute permission on the stored procedures. This is a good way of designing your database from a security point of view, since it means users can only perform the actions that you specify. However, if you use dynamic SQL then you also need to grant permissions on the underlying tables, meaning that users will have more access than you may wish them to have.

(3) Performance. It's more likely that execution plans will not be cached for queries executed through dynamic SQL, or that inappropriate plans will be re-used.

It can be cumbersome to have several stored procedures to access each table, but I would argue that it's worth the pain.

I'm not sure whether you're really using SELECT * FROM in your code, or whether it was just for illustration purposes. If you are using it, I'd recommend specifying a column list instead. This is so that you don't return more data than you really need, and to make your application less likely to break when you change the structure of your tables.

John

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

John Mitchell-245523 (10/5/2010)
(3) Performance. It's more likely that execution plans will not be cached for queries executed through dynamic SQL, or that inappropriate plans will be re-used.

The dynamic SQL strings will be treated exactly as ad-hoc SQL. An exec plan for each different string (where different is even a single whitespace character)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Gail, that's fair enough. I suppose what it means is that, since the WHERE clause is being supplied as a parameter to the stored procedure, we could end up with too many plans, and useful plans being aged out of the cache by spurious ones.

John

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

John Mitchell-245523 (10/5/2010)
I suppose what it means is that, since the WHERE clause is being supplied as a parameter to the stored procedure, we could end up with too many plans, and useful plans being aged out of the cache by spurious ones.

Absolutely. There are other downsides to lots and lots of ad-hoc SQL, especially if there are lots of database users and lots of memory on the server.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass