Automating record selection parameters

Hi,

I need some advise on how to automate record selection on a stored
proc. Here is my situation. I have a stored proc that I used on
Crystal reports with two parameters - Acctcode and Subacct. When a
user enters ' *' on these parameter, it means to report on all
accounts otherwise, report only on specific account.

Here is my select statement with line numbers:

Create proc rb_SubledgerRpt
@.Acctcode varchar(4), @.SubAcct varchar(3)

As

3 Select AcctCode, SubAcct
4 From GLDetails
5 Where SubAcct <> ' '
6 and AcctCode = @.Acctcode -- for specific acctcode
7 and SubAcct = @.SubAcct -- for specific subacct

8 Go

-- If a user wants to see all Acctcode, and all Subacct, how do I
disable lines 6 and 7?

Thank you in advance for your help.

EdgarHi Edgar,

How about:

Create proc rb_SubledgerRpt
@.Acctcode varchar(4), @.SubAcct varchar(3)
As
Select AcctCode, SubAcct
From GLDetails
Where SubAcct <> ' '
and ((@.AcctCode = '*' ) or (AcctCode = @.Acctcode))
and ((@.SubAcct = '*' ) or (SubAcct = @.SubAcct))

-Dick Christoph
"Edgar" <edgarjtan@.yahoo.com> wrote in message
news:1141058639.691125.36720@.v46g2000cwv.googlegro ups.com...
> Hi,
> I need some advise on how to automate record selection on a stored
> proc. Here is my situation. I have a stored proc that I used on
> Crystal reports with two parameters - Acctcode and Subacct. When a
> user enters ' *' on these parameter, it means to report on all
> accounts otherwise, report only on specific account.
> Here is my select statement with line numbers:
> Create proc rb_SubledgerRpt
> @.Acctcode varchar(4), @.SubAcct varchar(3)
> As
> 3 Select AcctCode, SubAcct
> 4 From GLDetails
> 5 Where SubAcct <> ' '
> 6 and AcctCode = @.Acctcode -- for specific acctcode
> 7 and SubAcct = @.SubAcct -- for specific subacct
> 8 Go
> -- If a user wants to see all Acctcode, and all Subacct, how do I
> disable lines 6 and 7?
> Thank you in advance for your help.
> Edgar|||One way would be this:

Declare @.sQry nvarchar(500)
Declare @.sWhere nvarchar(500)
Set @.sWhere = ' '
set @.AcctCode = 'ABC'
Set @.sQry =
'Select AcctCode, SubAcct ' +
'From GLDetails ' +
'Where SubAcct <> '' '''

If @.AcctCode <> '*'
Set @.sWhere = @.sWhere + 'and AcctCode = ''' + @.Acctcode + ''''

If @.SubAcct <> '*'
Set @.sWhere = @.sWhere + 'and SubAcct = ''' + @.SubAcct + ''''

Set @.sQry = @.sQry + @.sWhere
EXEC sp_executesql @.sQry|||Edgar (edgarjtan@.yahoo.com) writes:
> I need some advise on how to automate record selection on a stored
> proc. Here is my situation. I have a stored proc that I used on
> Crystal reports with two parameters - Acctcode and Subacct. When a
> user enters ' *' on these parameter, it means to report on all
> accounts otherwise, report only on specific account.
> Here is my select statement with line numbers:
> Create proc rb_SubledgerRpt
> @.Acctcode varchar(4), @.SubAcct varchar(3)
> As
> 3 Select AcctCode, SubAcct
> 4 From GLDetails
> 5 Where SubAcct <> ' '
> 6 and AcctCode = @.Acctcode -- for specific acctcode
> 7 and SubAcct = @.SubAcct -- for specific subacct
> 8 Go
> -- If a user wants to see all Acctcode, and all Subacct, how do I
> disable lines 6 and 7?

Dick and Jennifer suggested two methods. Dick's method is cleaner, but
Jennifer's solution can give better performance. If there are no
indexes on AcctCode or SubAcct (or the table is small), then there is
on performance issue. But if there are indexes, it is not likely that
Dick's solution will make use of them.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hmmmm.

I guess I don't understand your comments Mr. Sommarskog. Dick's
solution to me is better. It is simpler, and doesn't use dynamic SQL.

Further, I've had the best success with GL's using a composite index on
Account+Subaccount.
If you don't know the account, you will end up doing a table scan
pretty much no matter what. If you know the account and the subaccount,
retrievals can be VERY fast.

Finally, because Dick's solution doesn't use dynamic SQL, the optimizer
can key in on the best way to do the search relatively easily.

I look forward to hearing why you don't think the optimizer would
indexes. They have in my experience.

Thank you,
Doug|||Doug (drmiller100@.hotmail.com) writes:
> I guess I don't understand your comments Mr. Sommarskog. Dick's
> solution to me is better. It is simpler, and doesn't use dynamic SQL.
> Further, I've had the best success with GL's using a composite index on
> Account+Subaccount.
> If you don't know the account, you will end up doing a table scan
> pretty much no matter what. If you know the account and the subaccount,
> retrievals can be VERY fast.

I will have admit that I did not consider the particular business problem,
but more considered the general case. Let's say that you have something
like:

SELECT ...
FROM tbl
WHERE (indexedcol1 = @.vall OR @.val1 = '*')
AND (indexedcol2 = @.val2 OR @.val2 = '*')
AND (nonindexedcol = @.val3 OR @.val3 = '*')

This will most certainly table scan, even if @.val1 has a distinct value and
@.val3 is '*', because when the optimizer builds the plan it has no
knowledge what value the parameter will have. It can sniff parameter
values, but it cannot build plans that produce wrong results. So it must
be a table scan. (The exception in SQL 2005 is when you add the query hint
OPTION (RECOMPILE).)

> Finally, because Dick's solution doesn't use dynamic SQL, the optimizer
> can key in on the best way to do the search relatively easily.

If you build a dynamic SQL string and include only the the search
parameters that were actually given, you will give the optimizer the
exact right amount of information to work with. If you only supply
@.val1, the optimizer will use that index (if it is selective enough).
If you only supply @.val2, it will use thar index, and if you supply
only @.val3, the query will table scan.

For a longer discsussion on the topic, see my article
http://www.sommarskog.se/dyn-search.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hello,

Thanks for your comments.

An interesting subtlty is involved here. I wonder if the difference is
significant.
Dick's code had and ((@.AcctCode = '*' ) or (AcctCode = @.Acctcode))

your code had
(indexedcol1 = @.vall OR @.val1 = '*')

this sounds weird, but I can easily see where your code would require a
table scan, yet Dick's code could be done without one.
Does this make any sense to anyone else?
I don't know how to explain my logic.