August 29, 2011 at 7:00 am
I've been working as a Contractor for an organization documenting their stored procedures and processes. In over 90% of their stored procedures they use dynamic SQL for DML and to execute stored procedures. One technique they use is to store SQL in several fields in tables, and concatenate them in a string which is then executed. I've never seen this used anyplace else, never seen it in literature on the subject, nor anywhere on the Internet.
They also have stored procedures and input/output parameters stored in fields in tables which can be called and executed via a cursor.
I have a few questions regarding this technique:
1. Has anybody out there used a technique like this?
2. What are the performance and cost implications of constructing dynamic SQL using this method? I assume not very good on both.
3. Is it recommended, or is it just insane to use this method?
As an example here is an actual table and the actual stored procedure that pulls SQL from the table:
Table name: doc_access_rule_group
Fields and values (rule_grp_cd is the primary key):
rule_grp_cdselect_fieldsdesc_txtfrom_txtcondition_txt
1sag.access_group_cddefault access for one documentdbo.SYSTEM_ACCESS_GROUP sag, APPL_SYSTEM_ACCESS_GROUP agag.appl_id='SUPR' and ag.active_flg='Y' and ag.access_group_cd = sag.access_group_cd
2tprof.doc_id,sag.access_group_cdrederive default access for group of documentsdbo.SYSTEM_ACCESS_GROUP sag, APPL_SYSTEM_ACCESS_GROUP ag, @tp_profile tprofag.appl_id='SUPR' and ag.active_flg='Y' and ag.access_group_cd = sag.access_group_cd
Here is the stored procedure (their code, not mine):
[font="Courier New"]CREATE procedure [dbo].[p_Upload_Extract_defaults]
@tb_param varchar(255),
@result_tb varchar(255),
@security_type_cd int ,
@priority_lvl_ind int,
@default_rule int = 1,
@debug_ind char(1) = 'Y'
as
/*****************************************************************************************
-- Function: p_Upload_Extract_defaults : utility procedure to insert @insertedData into created table named @tblNm
-- Input: @tb_param is the following format: @tb_param='"@tp_profile","#tmp_profile"char(59)"@tp_property","#tmp_property"'
-- @result_tb: name of table to contain the result set
-- @security_type_cd: security_type_cd from dbo.security_type table
-- @default_rule: 1 for individual document, 2 for batch documents
-- Output: return code 0: selected information does not match any provided rules
-- > 0: number of access groups
--
-- Usage: Called from dbo.p_upload_extract_accessgroups procedure
-- Create DT: Develop by:Modification
-- 04/12/2006****Creation of this stored procedure
-- 08/22/2006****Added 'FRS' verification for each call
-- 07/10/2008 ****Adjust documents based on those identified in doc_access_adjustment table
-- 01/06/09****change variables to varchar(max) and for FRS only, exclude those dist 13 with access_type_cd of 15
-- 02/02/2009****For FRS only, change to exclude dist 13 with access_type_cd = 16 for International Finance group
-- 2/14/2011****For testing added insert to event record
***********************************************************************************************************/
Set nocount on
declare @sql varchar(MAX),
@total int,
@cnt int,
@rule_id smallint,
@select_sql varchar(max),
@from_sql varchar(max),
@condition_sql varchar(max),
@current_sql varchar(max),
@Ln char(1),
@rowsAffected int,
@current_access_type_cd int
select @ln = Char(13)
if (@debug_ind ='Y') PRINT 'START p_Upload_Extract_defaults'
create table #output
(total int not null)
create table #rules
(cnt int identity not null,
rule_id smallint not null,
access_type_cd smallint not null)
create table #tmp_tables(
parameter varchar(30) not null,
value varchar(50) not null)
insert into #rules
select distinct rule_id,access_type_cd
from dbo.doc_access_rules
where rule_grp_cd = @default_rule
and security_type_cd = @security_type_cd
and priority_lvl_ind = @priority_lvl_ind
and active_flg='Y'
select @total = @@rowcount
if (@debug_ind ='Y') select * from #rules
select @cnt = cnt, @rule_id = r.rule_id ,@current_sql = d.rule_sql_txt
from #rules r, dbo.doc_access_rules d where cnt = 1 and r.rule_id = d.rule_id
and rule_grp_cd = @default_rule ;
--select @select_sql = "select sag.access_group_cd, 7 as access_type_cd ";
select @select_sql= 'select ' + select_fields from dbo.doc_access_rule_group where rule_grp_cd = @default_rule
select @from_sql = ' from ' + from_txt from dbo.doc_access_rule_group where rule_grp_cd = @default_rule
select @condition_sql = ' where ' + condition_txt from dbo.doc_access_rule_group where rule_grp_cd = @default_rule
select @sql = '';
while @cnt <= @total
begin
select @current_access_type_cd = isnull(access_type_cd,-1) from
dbo.doc_access_rules where rule_id = @rule_id
and rule_grp_cd = @default_rule;
if (@cnt > 1) select @sql = rtrim(@sql) + " union " + @ln
select @sql = @sql + rtrim(@select_sql) + "," + rtrim(cast(@current_access_type_cd as varchar)) + rtrim(@from_sql) +
rtrim(@condition_sql) + " "+ rtrim(@current_sql) + @ln
select @cnt = @cnt + 1;
select @cnt = cnt, @rule_id = r.rule_id ,@current_sql = d.rule_sql_txt
from #rules r, dbo.doc_access_rules d
where cnt = @cnt and r.rule_id = d.rule_id
and rule_grp_cd = @default_rule;
end
exec dbo.P_Util_InsertedData '#tmp_tables',@tb_param
select @sql = 'insert into ' +@result_tb + ' ' + rtrim(@sql)
if (@default_rule = 1 ) -- DEED
begin
set @sql = rtrim(@sql) + ' insert into #output select count(*) from '+rtrim(@result_tb) + ' t, #rules r where t.access_type_cd = r.access_type_cd ' +
' delete ' + rtrim(@result_tb) + ' from '+@result_tb +' res, dbo.SYSTEM_ACCESS_GROUP sys
where sys.access_group_cd = res.access_id and res.access_type_cd = 7
and (sys.site_cd > 13 or (sys.site_cd = 13 and sys.access_type_cd = 16))
and exists (select 1 from @tp_property prop where prop.property_cd = 7
and prop.property_value_txt ="FRS") ' +
' delete ' + rtrim(@result_tb) +
' from '+rtrim(@result_tb) +' res, dbo.SYSTEM_ACCESS_GROUP sys , @tp_profile proffrs, dbo.doc_access_adjustment adj,
@tp_docid updt, dbo.system_access_group adj_sys
where sys.access_group_cd = res.access_id and
res.access_type_cd = 7 and
sys.site_cd > 13 and
updt.doc_id = adj.doc_id and
proffrs.default_access_flg="Y" and
adj_sys.access_group_cd = adj.access_group_cd and
adj_sys.role_cd = 1 and
adj_sys.access_type_cd = 10 and
adj_sys.site_cd = sys.site_cd and
adj.change_txt="REVOKE" ' +
' insert ' + rtrim(@result_tb) +
' select distinct access_grp.access_group_cd, 7
FROM
dbo.SYSTEM_ACCESS_GROUP adj_grp,
@tp_profile proffrs,
DBO.ACCESS_GROUP_CONTAINER C,
@tp_docid updt,
dbo.doc_access_adjustment adj,
dbo.system_access_group access_grp,
dbo.institution inst,' +
rtrim(@result_tb) + ' res ' +
' where adj.access_group_cd = adj_grp.access_group_cd and res.access_type_cd = 7 and
updt.doc_id=adj.doc_id and
adj_grp.site_cd > 13 and access_grp.site_cd = adj_grp.site_cd and
access_grp.role_cd = 1 and access_grp.access_type_cd = 10
and inst.entity_id = proffrs.entity_id and proffrs.home_page_id = 3
and c.ACCESS_IND = rtrim(inst.access_ind)
and proffrs.default_access_flg="Y"
and charindex(" "+rtrim(c.member_access_ind),access_grp.access_group_nm) > 0
and adj.change_txt="GRANT"
and not exists(select 1 from '+rtrim(@result_tb) +' added where added.access_id = access_grp.access_group_cd)
and not exists (select 1 from @tp_property prop where prop.property_cd = 7 and prop.property_value_txt ="FRS") '
end
if (@default_rule = 2 ) -- BATCH
begin
set @sql = @sql + ' insert into #output select count(*) from '+@result_tb + ' t, #rules r where t.access_type_cd = r.access_type_cd ' +
' delete ' + @result_tb + ' from '+@result_tb +
' res, dbo.SYSTEM_ACCESS_GROUP sys , @tp_profile proffrs
where sys.access_group_cd = res.access_id and res.access_type_cd = 7
and (sys.site_cd > 13 or (sys.site_cd = 13 and sys.access_type_cd = 16))
and res.doc_id = proffrs.doc_id
and exists (select 1 from @tp_property prop where prop.property_cd = 7
and prop.property_value_txt ="FRS" and proffrs.doc_id = prop.doc_id) '+
' delete ' + @result_tb +
' from '+@result_tb +' res, dbo.SYSTEM_ACCESS_GROUP sys , @tp_profile proffrs, dbo.doc_access_adjustment adj, dbo.system_access_group adj_sys
where sys.access_group_cd = res.access_id and
res.access_type_cd = 7 and
sys.site_cd > 13 and
proffrs.doc_id = adj.doc_id and
res.doc_id = proffrs.doc_id and
proffrs.default_access_flg="Y" and
adj_sys.site_cd = sys.site_cd and
adj_sys.access_group_cd = adj.access_group_cd and
adj_sys.role_cd = 1 and
adj_sys.access_type_cd = 10 and
adj.change_txt="REVOKE" '+
' insert ' + @result_tb +
' select distinct proffrs.doc_id, access_grp.access_group_cd, 7
FROM
dbo.SYSTEM_ACCESS_GROUP adj_grp,
@tp_profile proffrs,
DBO.ACCESS_GROUP_CONTAINER C,
dbo.doc_access_adjustment adj,
dbo.system_access_group access_grp,
dbo.institution inst,' + @result_tb + ' res ' +
' where adj.access_group_cd = adj_grp.access_group_cd and res.access_type_cd = 7
and res.doc_id = proffrs.doc_id and adj.doc_id = proffrs.doc_id
and proffrs.default_access_flg="Y"
and adj_grp.site_cd > 13 and access_grp.site_cd = adj_grp.site_cd and access_grp.role_cd = 1 and access_grp.access_type_cd = 10
and inst.entity_id = proffrs.entity_id and proffrs.home_page_id = 3
and c.ACCESS_IND = rtrim(inst.access_ind)
and charindex(" "+rtrim(c.member_access_ind),access_grp.access_group_nm) > 0
and adj.change_txt="GRANT"
and not exists(select 1 from '+@result_tb +' added where added.access_id = access_grp.access_group_cd and added.doc_id = proffrs.doc_id)
and not exists (select 1 from @tp_property prop where prop.property_cd = 7 and prop.property_value_txt ="FRS" and proffrs.doc_id = prop.doc_id) '
end
select @sql = replace(@sql,parameter,value)
from #tmp_tables
if (@debug_ind ='Y') print @sql
exec (@sql)
select @rowsAffected = total from #output
if (@debug_ind ='Y') PRINT 'END p_Upload_Extract_defaults'
return @rowsAffected
GO[/font]
August 29, 2011 at 10:00 am
Francis McFaul (8/29/2011)
I've been working as a Contractor for an organization documenting their stored procedures and processes. In over 90% of their stored procedures they use dynamic SQL for DML and to execute stored procedures. One technique they use is to store SQL in several fields in tables, and concatenate them in a string which is then executed. I've never seen this used anyplace else, never seen it in literature on the subject, nor anywhere on the Internet.They also have stored procedures and input/output parameters stored in fields in tables which can be called and executed via a cursor.
I have a few questions regarding this technique:
1. Has anybody out there used a technique like this?
2. What are the performance and cost implications of constructing dynamic SQL using this method? I assume not very good on both.
3. Is it recommended, or is it just insane to use this method?
There is probably a good reason you have never seen code like this...it is awful. Dynamic sql in itself is not an evil thing but using like this is. I could on for the next 14 paragraphs about things I would change and why this is so bad but you have to look no further than your understanding to get one of the main reasons this is so bad. It is really tough for somebody else to maintain it. Or course all the cursor and looping is terrible for performance too. That is going to be fun to rewrite....
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 29, 2011 at 10:17 am
Thank you for responding, Sean.
I should have been more precise in my original posting for everybody. I, myself, rarely use dynamic SQL, and never use cursors. I was referring to the part about storing SQL clauses or statements in fields in a table.
I think that the example of the rows from the table are hard to see in this format; please forgive me for this.
August 29, 2011 at 10:29 am
No I think I knew what you meant. Seems like an extra step that just simple isn't necessary. Stored procedures are a great way to "store" your sql. And they can take advantage of cached execution plans and a myriad of other benefits. There is probably something more subtle that I of course can't see but like you it sounds like a pretty bad implementation.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 29, 2011 at 10:47 am
Sean Lange (8/29/2011)
No I think I knew what you meant. Seems like an extra step that just simple isn't necessary. Stored procedures are a great way to "store" your sql. And they can take advantage of cached execution plans and a myriad of other benefits. There is probably something more subtle that I of course can't see but like you it sounds like a pretty bad implementation.
I call this "pessimization": an hourly contractor wants to max his billing. So he (usually it is a "he") writes code that works but has terrible performance and is difficult to maintain. In the next cycle he is called back -- he is the only one who "understands the problem domain" -- and then, maybe, he does it right and makes it run fast.
To state the hopefully obvious, that is not my way of doing things.
August 29, 2011 at 10:53 am
Revenant (8/29/2011)
Sean Lange (8/29/2011)
No I think I knew what you meant. Seems like an extra step that just simple isn't necessary. Stored procedures are a great way to "store" your sql. And they can take advantage of cached execution plans and a myriad of other benefits. There is probably something more subtle that I of course can't see but like you it sounds like a pretty bad implementation.I call this "pessimization": an hourly contractor wants to max his billing. So he (usually it is a "he") writes code that works but has terrible performance and is difficult to maintain. In the next cycle he is called back -- he is the only one who "understands the problem domain" -- and then, maybe, he does it right and makes it run fast.
To state the hopefully obvious, that is not my way of doing things.
That is hilarious!! I may have to "borrow" that term as it is a perfect fit!!! I can't tell you how many systems I have had the pleasure of unraveling with this type of design pattern.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 29, 2011 at 11:09 am
It should of course also go without saying that this just screams security problems.
Dynamic SQL in and of itself is a security loophole. There's ways to mitigate the risks, but they'll always be there.
This approach is not only using dynamic SQL, but it's using dynamic SQL that is "dynamic-ified" through parameters stored in a table. All it would take is for someone to change the values in that table and you could end up with a very big mess.
August 29, 2011 at 11:23 am
To SSCommitted,
This and other code was developed by their in-house Developers and has been around for years. Dynamic SQL are a fairly common method for them to employ. The techinique to store SQL clauses/statements in table is used in other systems at this organization. I think that most of their Developers are application developers who know some Transact-SQL. I haven't heard of any dedicated SQL Server database developers, only SS DBA's.
There are few Contractors here at all. I'm only here to document the stored procedures -- I'm not developing any stored procedures.
PS They also use "SELECT * FROM ...", and use T-SQL form of joins, rather than ANSI standard joins.
August 29, 2011 at 11:30 am
To answer your original questions then,
"1. Has anybody out there used a technique like this?"
I've never used a technique like this. I have at times constructed dynamic SQL queries using values stored in tables, but I've never stored the actual queries in tables.
"2. What are the performance and cost implications of constructing dynamic SQL using this method? I assume not very good on both."
Actually, performance and cost-wise, this isn't that bad of a solution. Querying the table to extract the query that is to be executed is probably fairly cheap, especially if it's properly indexed, and executing the dynamic query that is built is also probably fairly cheap, compared to other approaches. So from this point of view I wouldn't really have any justifiable means to complain.
"3. Is it recommended, or is it just insane to use this method?"
I'd go with the "just insane" answer. There's many, many problems associated to this type of solution.
The biggest problem is security. Having dynamic SQL is a security flaw in and of itself, but is one which can be overcome through security measures. One "easy" solution is to just run a script that checks the query for the presense of statements like "INSERT" or "EXEC". A more robust solution is to use the sp_executeSQL stored procedure to execute dynamic SQL, instead of just using EXEC. This lets you ensure the query is legitimate.
Another problem is that storing the values in the tables means that if you want to change your query, you have to actually dig in to the table and modify the line containing it. If the table definitions change, or if the data types change, or etc ..., then it can become a major headache to maintain later.
August 29, 2011 at 11:31 am
Francis McFaul (8/29/2011)
To SSCommitted,This and other code was developed by their in-house Developers and has been around for years. Dynamic SQL are a fairly common method for them to employ. The techinique to store SQL clauses/statements in table is used in other systems at this organization. I think that most of their Developers are application developers who know some Transact-SQL. I haven't heard of any dedicated SQL Server database developers, only SS DBA's.
There are few Contractors here at all. I'm only here to document the stored procedures -- I'm not developing any stored procedures.
PS They also use "SELECT * FROM ...", and use T-SQL form of joins, rather than ANSI standard joins.
So basically sloppy and crappy code is their SOP??? Good thing you are a consultant so you can run away quickly. Seriously, you have three choices as I see it:
1) educate them as to how horrible the way they these things and WHY it is bad.
2) When in Rome...
3) look for another consulting gig.
Educating them and actually taking one of those horrid process apart and rebuilding as properly designed process will demonstrate without a doubt the performance issues they are having. All too often I have been able to demonstrate to a client how poor performance is and they didn't even realize they had a problem. They just accept the slowness as part of the deal because they are accessing some complicated data. :Whistling: If they are open minded about your suggestions you might be able to land a long term consulting spot and redesign all their nonsense.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 29, 2011 at 11:45 am
Sean Lange (8/29/2011)
Francis McFaul (8/29/2011)
To SSCommitted,This and other code was developed by their in-house Developers and has been around for years. Dynamic SQL are a fairly common method for them to employ. The techinique to store SQL clauses/statements in table is used in other systems at this organization. I think that most of their Developers are application developers who know some Transact-SQL. I haven't heard of any dedicated SQL Server database developers, only SS DBA's.
There are few Contractors here at all. I'm only here to document the stored procedures -- I'm not developing any stored procedures.
PS They also use "SELECT * FROM ...", and use T-SQL form of joins, rather than ANSI standard joins.
So basically sloppy and crappy code is their SOP??? Good thing you are a consultant so you can run away quickly. Seriously, you have three choices as I see it:
1) educate them as to how horrible the way they these things and WHY it is bad.
2) When in Rome...
3) look for another consulting gig.
Educating them and actually taking one of those horrid process apart and rebuilding as properly designed process will demonstrate without a doubt the performance issues they are having. All too often I have been able to demonstrate to a client how poor performance is and they didn't even realize they had a problem. They just accept the slowness as part of the deal because they are accessing some complicated data. :Whistling: If they are open minded about your suggestions you might be able to land a long term consulting spot and redesign all their nonsense.
As a matter of client mamagement tactic, I would recommend that the (cued) suggestion that you review the existing code and make suggestions for improvement comes from the client, perhaps reluctantly, not from you.
August 29, 2011 at 11:51 am
Well, 2) is definitely out of the question. They're asked me to stay beyond my 6 month committment, and I said "No, thank you" -- mainly because their stored procedures are so poorly written, overly complicated, and riddled with cursors (I've used the term "Rube Goldberg" in meetings). As part of my documentation they've asked me to make suggestions on improving their code -- and I have.
In my 10 years working with SQL Server I've just never seen this particular technique before, so I wanted to research it as much as possible before commenting on it.
I appreciate these and any future comments on this technique
August 29, 2011 at 12:21 pm
Francis McFaul (8/29/2011)
Well, 2) is definitely out of the question. They're asked me to stay beyond my 6 month committment, and I said "No, thank you" -- mainly because their stored procedures are so poorly written, overly complicated, and riddled with cursors (I've used the term "Rube Goldberg" in meetings). As part of my documentation they've asked me to make suggestions on improving their code -- and I have.In my 10 years working with SQL Server I've just never seen this particular technique before, so I wanted to research it as much as possible before commenting on it.
I appreciate these and any future comments on this technique
I've seen this technique before. One of the purposes is to avoid testing scenarios. Usually, somewhere in the prod servers, is a front end app that they can 'adjust' the code from. It doesn't need to go through SDLC and is commonly used by front end developers who are trying to use AGILE in the method that says "We'll figure out the specs as we go, hopefully by rollout". Dynamic SQL stored anywhere else would require a deployment. Editing a field in a table probably avoids some specific SLDC rules they have and allows them to keep the client (business) happy by making quick changes.
Other options include some standardization, but when you start 'standardizing' dynamic SQL I start to cringe internally.
Good luck.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 29, 2011 at 1:29 pm
I have a few questions regarding this technique:
1. Has anybody out there used a technique like this?
2. What are the performance and cost implications of constructing dynamic SQL using this method? I assume not very good on both.
3. Is it recommended, or is it just insane to use this method?
1. I've used dynamic SQL before typically when I have one command I want to run on a bunch of different objects, for example if i wanted to rebuild all the indices in a database I could query the system table to get all the indices then use dynamic SQL to generate the rebuild command for each index.
2. Performance wise it would depend on how complex the queries are that they're using to generate the dynamic SQL and how complex the generated commands are. Dynamic SQL isn't inherently slow but their implementation of it seems fairly convoluted.
3. As with most tools when used properly there's nothing wrong with dynamic SQL, their method seems fairly insane at first glance though.
August 30, 2011 at 6:23 am
I have never stored the text of a procedure in a table.
I did once have a system that stored the names of procedures in a table, and would use it to in middle tier code to call the specific proc for the object. The procs were basically just SELECT code, and I was using reflection in the front end and middle tier to determine the type of object to populate. This method allowed me to write one or two generic data access objects, and made adding new objects fairly simple (along with not requiring a full redeployment of the entire code base).
It worked relatively well, but it required a specific naming convention for the objects. Performance-wise, it wasn't much of an issue because I wasn't actually using dynamic SQL in the code (the calls to the stored procs were from the actual application objects themselves).
Storing the full code in a table seems like a maintenance nightmare, and also a testing and debugging pain in the rear end. Currently, I have a few procs that build a dynamic SQL string within them, and it can get a bit tricky to determine why the results aren't always what you expect.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply