Posted by Erland Sommarskog on 02/28/06 01:13

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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]