SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need help on select sceanrio


Need help on select sceanrio

Author
Message
Kool123
Kool123
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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.....
Steve Jones
Steve Jones
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: Administrators
Points: 148428 Visits: 19444
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
My Blog: www.voiceofthedba.com
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228317 Visits: 46341
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

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


Kool123
Kool123
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228317 Visits: 46341
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

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


Kool123
Kool123
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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....
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228317 Visits: 46341
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

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search