hi, i am Create a search page"VB 6" which takes parameters as passing values from 6 different textboxes
on click ok the result should get in a Grid
User May be Enter 1 or 2 or 3 or all 6 values with different Combination
Kindly Help me how can I control Querey
Regards
Fakhruddin
Well, there are lots of variations for that.
Can you post some information about your table(s) the query you're using?
|||Here you have a couple of outstanding articles about that theme.
Dynamic Search Conditions in
AMB
|||yet another split problem.. here's another resource|||Why dynamic SQL here, You can do it straight forward,
Have a 6 Parameters in the SP.
Pass the each value from the textbox (if user doesn't enter any value for the text box, pass NULL).
Then use the following code on your sp,
Code Snippet
Create proc SearchData
(
@.Param1 as Varchar(100) = null,
@.Param2 as Varchar(100) = null,
@.Param3 as Varchar(100) = null,
@.Param4 as Varchar(100) = null,
@.Param5 as Varchar(100) = null,
@.Param6 as Varchar(100) = null
)
As
Begin
Select
..
..
From
Tables ...
Where
(@.Param1 is NULL or Column1 = @.Param1)
And (@.Param2 is NULL or Column2 = @.Param2)
And (@.Param3 is NULL or Column3 = @.Param3)
And (@.Param4 is NULL or Column4 = @.Param4)
And (@.Param5 is NULL or Column5 = @.Param5)
And (@.Param6 is NULL or Column6 = @.Param6)
End
|||Hi Manivannan.D.Sekaran,
I wish it is as simple as that, but not, it is not. Depend on the indexes existing for the table in question, the kind of expression used in the "where" clause and the selectivity of the indexes based on the parameters value, the approach you use will be very important regarding to performance. The expression you used, does not yield good performance when there is and index by that column with a high selectivity. See the execution plan of the following examples:
Code Snippet
use northwind
go
create procedure dbo.p1;1
@.orderid int = null
as
set nocount on
select orderid, customerid, orderdate
from dbo.orders
where orderid = @.orderid or @.orderid is null
go
create procedure dbo.p1;2
@.orderid int = null
as
set nocount on
if @.orderid is null
select orderid, customerid, orderdate
from dbo.orders
else
select orderid, customerid, orderdate
from dbo.orders
where orderid = @.orderid
go
set showplan_text off
go
exec dbo.p1;1 10250
go
exec dbo.p1;2 10250
go
set showplan_text off
go
drop procedure dbo.p1
go
You will find a deep analysis in the articles, written by Erland Sommarskog, I posted in my previous message.
AMB
|||Mani,
I confess that I often use the method you posted (or at least a variation using coalesce( @.Param, Column). I limit my usage to known 'smallish' tables where I am satisfied there is little probability of index usage.
The warning that Erland makes in his research/articles is that when the tables are large, the query plan may be totally wrong for various combinations of supplied parameters when using this approach.
So yours is a decent suggestion, yet one that should be presented with a caveat about the potential issues.
Hunchie's correct in providing example code so that all reading this thread can examine the issues for themselves. If you add the following code to Hunchie's example, you will see that the coalesce() option has a more efficient execution plan than the
option. However, the 'else' option produces the 'best' plan of the three methods.
(@.Param1 is NULL or Column1 = @.Param1)
All that said, sometimes the simplicity of the coalesce option with small data sets makes it a choice to consider.
Code Snippet
create procedure dbo.p1;3
@.orderid int = null
as
set nocount on
select orderid, customerid, orderdate
from dbo.orders
where orderid = coalesce( @.orderid, orderid )
go
exec dbo.p1;3 10250
go
