Reasonable alternative to Dynamic SQL?

I have a web form that contains 7 search boxes. Any 2 can be used to search,
but only 2 out of the 7.
Since I dont know which ones will be used for search criteria, I figure
using Dynamic SQL will be the best solution and add WHERE clauses as needed.
However, the query is somewhat intense, and I worry about execution time.
Is there an alternative to this that I dont know about?SQL Server MVP Erland Sommarskog has written an excellent article on dynamic
searches and one of his conclusions is that dynamic SQL can be the right
choice from a performance poitn of view in some circumstances. you can find
his article here:
http://www.algonet.se/~sommar/dyn-search.html
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Learning SQL Server" <new@.bie.com> wrote in message
news:OHvm6aQVDHA.2200@.TK2MSFTNGP09.phx.gbl...
> I have a web form that contains 7 search boxes. Any 2 can be used to
search,
> but only 2 out of the 7.
> Since I dont know which ones will be used for search criteria, I figure
> using Dynamic SQL will be the best solution and add WHERE clauses as
needed.
> However, the query is somewhat intense, and I worry about execution time.
> Is there an alternative to this that I dont know about?
>
>|||Jacco:
Excellent is an understatement. Thank you for this great resource.
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:O0PRU0QVDHA.2068@.TK2MSFTNGP11.phx.gbl...
> SQL Server MVP Erland Sommarskog has written an excellent article on
dynamic
> searches and one of his conclusions is that dynamic SQL can be the right
> choice from a performance poitn of view in some circumstances. you can
find
> his article here:
> http://www.algonet.se/~sommar/dyn-search.html
> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Database Administrator
> Eurostop Ltd.
>
> "Learning SQL Server" <new@.bie.com> wrote in message
> news:OHvm6aQVDHA.2200@.TK2MSFTNGP09.phx.gbl...
> >
> > I have a web form that contains 7 search boxes. Any 2 can be used to
> search,
> > but only 2 out of the 7.
> >
> > Since I dont know which ones will be used for search criteria, I figure
> > using Dynamic SQL will be the best solution and add WHERE clauses as
> needed.
> > However, the query is somewhat intense, and I worry about execution
time.
> >
> > Is there an alternative to this that I dont know about?
> >
> >
> >
>

Really confused in SQL statement

Hi there,

I'd like to execute an SQL query but i can not find a suitable way.Well i have one table contains :

Table 1 :
Id, CustNo, CustName, CustSurName, CustId1, CustId2,CustId3

Second Table contains :

Table 2 :

Id, WhNo, WhName, WhSurName, CustId1, CustId2,CustId3

As you see CustId1, CustId2,CustId3 is common.I would like to combine and show them in one table. It is simple with :
select a.* , b.* from Table1 a Table2 b ......(blah blah blah)

I select CustName rows from Table1 and can choose CustId1, CustId2, CustId3.But if i choose CustName (Customer Name) and it returns more than one row i can not match CustId's from Table2 and display in merged Query object.

Let me give an example :

select * from Table1 (where CustName = 'Joe') ;

gives these results :

Id, CustNo, CustName, CustSurName, CustId1, CustId2,CustId3

1,1,Joe,Black,1,1,1
2,2,Joe,Green,1,1,2
3,3,Joe,Blue,1,1,3

select * from Table2

gives these results :

Id, WhNo, WhName, WhSurName, CustId1, CustId2,CustId3

1,1,1,Apple,Peach,1,1,1
1,1,1,Apple2,Peach2,1,1,1
1,1,1,Apple3,Peach3,1,1,2
1,1,1,Apple4,Peach4,1,1,2
1,1,1,Apple5,Peach5,1,1,2
1,1,1,Apple6,Peach6,1,1,3
1,1,1,Apple7,Peach7,1,1,3
1,1,1,Apple8,Peach8,1,1,3
1,1,1,Apple9,Peach9,1,1,3
1,1,1,Apple10,Peach10,1,1,3

As you see 1,1,1,Joe,Black has 2 records in Table2 1,1,2,Joe,Green has 3 records in Table2 finally 1,1,3,Joe,Blue has 5 records in Table2.
Now i would like to select only name in Table1 and display merged table like this :

Table1 and Table 2 : (for example i choose "Joe" from Table 1 and the result is

1,1,Joe,Black,1,1,1,Apple,Peach
1,1,Joe,Black,1,1,1,Apple2,Peach2
2,2,Joe,Green,1,1,2,Apple3,Peach3
2,2,Joe,Green,1,1,2,Apple4,Peach4
2,2,Joe,Green,1,1,2,Apple5,Peach5
.
.
.
(goes like this)

As you see i want to select name from Table 1 and it should give merged table.I only have CustId1,CustId2,CustId3 as common field.

Thanks in advance.

AnalyzerTry this:

Select a.Id, a.CustNo, a.CustName, a.CustSurName
, a.CustId1, a.CustId2, a.CustId3
, b.WhName, b.WhSurName
From Table1 a, Table2 b
Where b.CustId3 = a.CustId3;

;)|||Hi,

Thank you for your reply but i can not select my list with this code because first of all i would like to select by name then Table 1 will return CustId numbers.Finally i'd like to make a merged list which contains Table1+Table2 values :(
Pat adviced me to use inner join for this.What is it?

Analyzer

P.S. By the way i didnt see an SQL section in the forum and i cross posted.I apologize for this.|||Which RDBMS software are you using?|||Well i am using MySQL 4.0+Borland C++ Builder 6.0 Ent+Zeos DBO 6.1.5|||Try something like:SELECT *
FROM table1
INNER JOIN table2
ON (table2.custId1 = table1.custId1
AND table2.custId2 = table1.custId2
AND table2.custId3 = table1.custId3)-PatP|||Hi Pat,

Thank you for your reply.But first i want to search on Customer Name then i would like to select columns from table2.I wrote this code :

SELECT * FROM table1 WHERE (CustName="John" and CustSurName="Blue") INNER JOIN table2 ON (table1.CustId1 = table2.CustId1 AND table1.CustId2 = table2.CustId2 AND table1.CustId3 = table2.CustId3)

But it does not work.Where is my fault?
I would like to explain more.For example John Blue purchased a printer, 2 DSL Modem and 1 monitor.I would like to search who named "John" and what did he purchased.
First of all i need to search name then i will search on CustId1,CustId2,CustId3.

I found this page : http://www.w3schools.com/sql/sql_join.asp but it does not elaborate.

Analyzer.|||Ok, i did it :)

SELECT * FROM table1 INNER JOIN table2 ON (table1.CustId1 = table2.CustId1 AND table1.CustId2 = table2.CustId2 AND table1.CustId3 = table2.CustId3) WHERE (CustName="John" and CustSurName="Blue")

Thank you for your all help.I appreciated it much.

Analyzer

Readymade Database Required

Hi..

I need a readymade database of sql server 2005 which contains city, state and country

is there any way to get it?

Here's one, seems to be quite extensive.
http://www.geodatasource.com/cities-free.html