March 1, 2012 at 3:43 am
Hi;
I want dynamically search condition.
This is my table(This is generated dynamically ) this is not a physical table.
id Tablename columnname Value
1 Company Company_name Microsoft
2 Address Pcity CA
3 Phone Pnumber 100-4582
I want search the Value in the particular table , In this tables are already in the database(Company,Address,Phone). dynamically pass the tablename and columnname and search the Value.
Ex
Select c.Company_name from Company c
join Address a on a.companyid=c.companyid
join phone p on p.companyid=c.companyid
where 1=1
and c.company_name like '%Microsoft%'
and a.Pcity Like '%CA%'
and p.Pnumber like '%100-4582%'
I want dynamically buld the query and search the condition in the Value column.
How can I do this ..
Thanks.
March 1, 2012 at 7:07 am
Being too dynamic is not a good thing. It has it's pitfalls.
If you can solve something with static sql, do not use dynamic sql.
If you absolutely cannot avoid dynamic sql, than at least use parameters (sp_executesql).
That is: do not concatenate values - send them as parameters.
Unfortunately, table names and other object identifiers you cannot send as parameter and you will have concatenate them (if they are not known at design time).
Building a dynamic WHERE condition is not a bad idea.
Here are some basic examples:
http://sqlserverpedia.com/wiki/Stored_Procedures_-_Executing_%26_Dynamic_SQL
March 1, 2012 at 7:56 am
Take look at this post. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply