Passing Multiple Parameters

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 T-SQL

http://www.sommarskog.se/dyn-search.html

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

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

(@.Param1 is NULL or Column1 = @.Param1)

option. However, the 'else' option produces the 'best' plan of the three methods.

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

|||It is definitely important that you give more information. If the text boxes are not related, there is one solution, and if they are an array, there is another. Also, if there are millions of rows, you may have to do some other things (like using dynamic SQL or multiple stored procedures.)