September 8, 2012 at 6:46 am
I have one config table which has my where clause which is needed in my select statement.
E.G
Select * from MyTable where abc=123
The condition 'abc=123' is in my Config table which i need to call in my statement to make complete select statement.
like (Select * from MyTable where (select config_value from Config_table)) but this wont work.
I need to write the procedure for above scenarion having many such statement.
Can anybody please help me in this issue.....
September 8, 2012 at 9:22 am
The way to do this is with dynamic SQL.
declare @C varchar(500)
select @C = 'Select * from MyTable where '
+ config_value from Config_table
exec(@c)
Be aware that if you are building this string with other values, you are potentially opening yourself up to SQL injection and security issues.
Can I ask why you are storing the WHERE filters in a table?
September 8, 2012 at 9:22 am
Honestly, I would strongly recommend you don't go that route. It's going to be messy, a pain to code and maintain and a huge security risk.
The only way to do that is dynamic SQL. Be sure to read up on SQL Injection first.
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
September 10, 2012 at 1:07 am
Thnx Steve n GilaMonster.....
What i have done is i have created below SP;
declare @WhereCond varchar(max)
declare @BeforeWhere varchar(max)
declare @AfterWher varchar(max)
declare @totalquery nvarchar(max)
set @BeforeWhere=
'select * from Mytable where'
select @WhereCond=CNFGRTN_VAL from CONFIG_TABLE
set @totalquery=@BeforeWhere+@WhereCond
insert into #TEMPtable execute sp_executesql @finalQuery
select * from #TEMPtable
And this SP i have called in SSIS OLEDB source. Please let me know wheter this would be working for large dataset.
September 10, 2012 at 1:55 am
Work, yes it will. A good idea, no, very much not.
It's a security risk and it smells of bad design. Why do you want to go this route?
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
September 10, 2012 at 1:58 am
No choice for me....my Client want that way....
If there is another way to handle this please let me know....
September 10, 2012 at 2:48 am
I'd say it's your job as consultant to advise the client against this approach.
There's no way to do this without dynamic SQL. You need to account for the security risks, it's more development time often, harder to debug, more prone to errors, etc.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply