Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help on select sceanrio Expand / Collapse
Author
Message
Posted Saturday, September 8, 2012 6:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 11:16 PM
Points: 7, Visits: 135
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.....
Post #1356365
Posted Saturday, September 8, 2012 9:22 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:02 PM
Points: 33,062, Visits: 15,176
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?








Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1356375
Posted Saturday, September 8, 2012 9:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 42,451, Visits: 35,506
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 2008, MVP
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

Post #1356376
Posted Monday, September 10, 2012 1:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 11:16 PM
Points: 7, Visits: 135
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.

Post #1356576
Posted Monday, September 10, 2012 1:55 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 42,451, Visits: 35,506
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 2008, MVP
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

Post #1356585
Posted Monday, September 10, 2012 1:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 11:16 PM
Points: 7, Visits: 135
No choice for me....my Client want that way....

If there is another way to handle this please let me know....
Post #1356587
Posted Monday, September 10, 2012 2:48 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 42,451, Visits: 35,506
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 2008, MVP
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

Post #1356596
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse