Need help on select sceanrio

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Need help on select sceanrio

Need help on select sceanrio

Rate Topic

Display Mode

Topic Options

Author

Message

Kool123

Posted Saturday, September 08, 2012 6:46 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 3:55 AM
Points: 7, Visits: 101

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

Steve Jones - SSC Editor

Posted Saturday, September 08, 2012 9:22 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 5:09 AM
Points: 31,526, Visits: 13,864

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

GilaMonster

Posted Saturday, September 08, 2012 9:22 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:20 AM
Points: 38,089, Visits: 30,382

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

Kool123

Posted Monday, September 10, 2012 1:07 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 3:55 AM
Points: 7, Visits: 101

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

GilaMonster

Posted Monday, September 10, 2012 1:55 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:20 AM
Points: 38,089, Visits: 30,382

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

Kool123

Posted Monday, September 10, 2012 1:58 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 3:55 AM
Points: 7, Visits: 101

No choice for me....my Client want that way....

If there is another way to handle this please let me know....

Post #1356587

GilaMonster

Posted Monday, September 10, 2012 2:48 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:20 AM
Points: 38,089, Visits: 30,382

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 »

Permissions