January 18, 2016 at 1:18 am
Hi All,
I have a requirement to prepare the sql dynamically in order to use the table record values in joins.
Sample data as below.
CREATE TABLE tmpConfig
(
Action VARCHAR(255),
Dimension VARCHAR(255),
Field VARCHAR (255),
Hierarchy VARCHAR(255),
Level VARCHAR(255),
Value VARCHAR(255)
)
insert into tmpConfig (Action,Dimension,Field,Hierarchy,Level,Value)
select 'Exclude','Brand','Brand_id','mart.t_mart_isscom_trademark_hier','Brand_id','2675,612' union all
select 'Include','Product','Product_id','mart.t_mart_isscom_trademark_hier','Product_id','675,162'
I have to read the tmpConfig table (above) record by record then assign the value to a variable.
For example: My first records is like below
ActionDimensionField Hierarchy Level Value
ExcludeBrandBrand_idtrademark_hierBrand_id2675,612
If "Action" is Exclude it should replace with "not In" ,Include - In ,then assign this to one variable(@action).
If dimension = Brand then @dim = 'Brand', @filed = field,@val = value from tmpConfig
I have to use those variables dynamically in my query as below: (Syntax may be in correct)
select
* from Xtable x
Inner Join '+@dim+' a with (nolock)
ON x.ID = a.'+@field+'
where '+@field+' '+@action+'('+@val+')
Can anyone can help me on how to get these?
January 18, 2016 at 5:55 am
The correct but bad answer is to start with DECLARE CURSOR and OPEN CURSOR, plus initializing some string variables. Then you enter a loop that uses FETCH to get the next row from the cursor and concatenates the right values to the string variables. After the loop, close and deallocate the cursor, then use EXEC () to execute the dynamic SQL. All the statements mentioned are described in Books Online.
The much better answer (which you probably do not want to hear, but I am going to give it anyway) is to throw away everything you have and rethink your entire design.
Using dynamic SQL is a well-known security risk. Google "SQL injection", or read http://www.sommarskog.se/dynamic_sql.html. Every hacker will have standard SQL injection tricks somewhere on the top of things to try. Using sp_executesql instead of EXEC can alleviate the risk, but only if the user-suppllied values are all replaced with parameters. You want table and column names to be dynamic, and they cannot be replaced by parameters - so you will still be vulnerable to injection attacks even if you do use sp_executesql.
Even worse than the injection risk, is that driving all the logic from a table makes your system completely unmaintainable and untunable.
If you decide to do go ahead and implement the mess you are planning, then at least make sure that whoever inherits the code will never find out where you live. For they will hate your guts.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply