|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
paramaeters in SPHi,
I have a Select query I have put in a stored procedure. What I would really like to do is pass it parameters that tell it not only the values to filter by but the fields to filter on too (choice of three including combination of more than one). Cannot figure it out! Any help welcome. SP eventually called by VB6 program Have already sucesfully passed it date paramaeters Thanks! You're looking for dynamic query.
I suggest you take a look at Erland's article for pro/cons. http://www.sommarskog.se/dyn-search.html -- Show quote-oj "Stuart Hawkins" <StuartHawk***@discussions.microsoft.com> wrote in message news:BA78DE3E-A2E3-4E6C-9D88-2B9ABD99F406@microsoft.com... > Hi, > I have a Select query I have put in a stored procedure. What I would > really > like to do is pass it parameters that tell it not only the values to > filter > by but the fields to filter on too (choice of three including combination > of > more than one). > > Cannot figure it out! Any help welcome. > > SP eventually called by VB6 program > Have already sucesfully passed it date paramaeters > > Thanks! Brilliant. Chers mate.
Show quote "oj" wrote: > You're looking for dynamic query. > > I suggest you take a look at Erland's article for pro/cons. > > http://www.sommarskog.se/dyn-search.html > > > -- > -oj > > > > "Stuart Hawkins" <StuartHawk***@discussions.microsoft.com> wrote in message > news:BA78DE3E-A2E3-4E6C-9D88-2B9ABD99F406@microsoft.com... > > Hi, > > I have a Select query I have put in a stored procedure. What I would > > really > > like to do is pass it parameters that tell it not only the values to > > filter > > by but the fields to filter on too (choice of three including combination > > of > > more than one). > > > > Cannot figure it out! Any help welcome. > > > > SP eventually called by VB6 program > > Have already sucesfully passed it date paramaeters > > > > Thanks! > > > Stuart Hawkins wrote:
> Hi, Or you can use something like the following if the permutations are > I have a Select query I have put in a stored procedure. What I would > really like to do is pass it parameters that tell it not only the > values to filter by but the fields to filter on too (choice of three > including combination of more than one). > > Cannot figure it out! Any help welcome. > > SP eventually called by VB6 program > Have already sucesfully passed it date paramaeters > > Thanks! limited: Select col1 From dbo.MyTable Where (col1 = @param1 or @param1 is null) and (col2 = @param2 or @param2 is null) and (col3 = @param3 or @param3 is null) You'll have to verify performance is adequate by testing all 6 combinations and looking at the execution plans. On my database, this construct always performs a clustered index scan,
regardless of the values of the parameters. There are many indexes on the table, but the optimizer does not choose them. Show quote "David Gugick" <david.gugick-nospam@quest.com> wrote in message news:uqC9qTsfFHA.3256@TK2MSFTNGP12.phx.gbl... > Stuart Hawkins wrote: > > Hi, > > I have a Select query I have put in a stored procedure. What I would > > really like to do is pass it parameters that tell it not only the > > values to filter by but the fields to filter on too (choice of three > > including combination of more than one). > > > > Cannot figure it out! Any help welcome. > > > > SP eventually called by VB6 program > > Have already sucesfully passed it date paramaeters > > > > Thanks! > > Or you can use something like the following if the permutations are > limited: > > Select > col1 > From > dbo.MyTable > Where > (col1 = @param1 or @param1 is null) > and > (col2 = @param2 or @param2 is null) > and > (col3 = @param3 or @param3 is null) > > You'll have to verify performance is adequate by testing all 6 > combinations and looking at the execution plans. > > -- > David Gugick > Quest Software > www.imceda.com > www.quest.com > That can happen. If you're scanning a non-clustered index, it's a bit better
than a clustered index scan or table scan. It should provide better performance than using coalesce and may be the only choice before heading to dynamic sql. But as you've seen performance testing all your queries is important in determining whether they operate in a manner acceptable to a server. Show quote "Brian Selzer" <br***@selzer-software.com> wrote in message news:uoZ2Sy5fFHA.3584@TK2MSFTNGP10.phx.gbl... > On my database, this construct always performs a clustered index scan, > regardless of the values of the parameters. There are many indexes on the > table, but the optimizer does not choose them. > > "David Gugick" <david.gugick-nospam@quest.com> wrote in message > news:uqC9qTsfFHA.3256@TK2MSFTNGP12.phx.gbl... >> Stuart Hawkins wrote: >> > Hi, >> > I have a Select query I have put in a stored procedure. What I would >> > really like to do is pass it parameters that tell it not only the >> > values to filter by but the fields to filter on too (choice of three >> > including combination of more than one). >> > >> > Cannot figure it out! Any help welcome. >> > >> > SP eventually called by VB6 program >> > Have already sucesfully passed it date paramaeters >> > >> > Thanks! >> >> Or you can use something like the following if the permutations are >> limited: >> >> Select >> col1 >> From >> dbo.MyTable >> Where >> (col1 = @param1 or @param1 is null) >> and >> (col2 = @param2 or @param2 is null) >> and >> (col3 = @param3 or @param3 is null) >> >> You'll have to verify performance is adequate by testing all 6 >> combinations and looking at the execution plans. >> >> -- >> David Gugick >> Quest Software >> www.imceda.com >> www.quest.com >> > > You need to learn that fields and columns are totally different
concepts. Also a slect is done in parallel while the term filter is used in sequential file system for record processing. This will let you skip parameters by passing a NULL. SELECT a ,b,c,d, .. FROM Foobar WHERE a = COALESCE (@parm_a, a) AND b = COALESCE (@parm_b, b) AND c = COALESCE (@parm_c, c); And of course you did not really mean that you want to pick "the fields [sic] to filter on too" on the fly since that would violate basic software engineering principles about coupling and cohesion, which is far, far more fundamental than SQL programming.  |
|||||||||||||||||||||||
Other interesting topics