How to use a parameter to return all records

Hot Topic

Vacumming Question
SQL Server 2005 Service Pack 1 is now available
DataReader Source and Column Types
how to use sqliostress.exe on remote server?
sql2005 database restore from another sql2005 backup file error.
How to connect from Sql Manager to a sql server 2000 database at an ISP
SQL Server wont start (after ALTER DATABASE for model db)
Conditional Insertion & Updation in Destination Table (SSIS)
Problem with Dependencies and the error "Cannot find the remote service"
Calling SSIS from VB Script

SQL Server

Convert MS Access(*,mdb) to SQL Server Database
Get Sql Err Message 15422 when activating application role.
Directly printing Report from RS without previewing
Database is read-only.
MDAC and SQL Server 2005
Executing an external process() in SQLCLR Project
Transfer SQL Server Objects Task
The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.
SQL Server default file directory and authentication mode
Tool from SQL 2000, Missing in SQL 2005?

How to use a parameter to return all records

I am using the example from the Microsoft Official  Course 2030A.

I want the option for a user to select valeu from a drop down to return all records or to choose individual ones from the multi-value check box.

My query is taking forever and as you see I just want the top ten

Select TOP 10 * from PRH_EOB

WHERE (MemberId = @MemberId OR @MemberId = 'ALL')

and (disenr_st = @Disenroll or @Disenroll = 'ALL')

and (LOB in (Select * from SplitList(',',@LOB) as ListItem) or @LOB = 'ALL')

and (Groupid in (Select * from SplitList(',',@GroupList) as ListItem) or @Grouplist = 'ALL')

and paydate between @From and @TO

order by Slastname, sFirstname, claimid, linenum

Thanks,
Phil

Answer this question

How to use a parameter to return all records

  • Don Stuber

    Come to find out, I have to put an index hint in the query to get it to work.

    ex

    Select * from PRH_EOB (INDEX = IX_PayDate)

    WHERE (MemberId = @MemberId OR @MemberId = 'ALL')

    and (disenr_st = @Disenroll or @Disenroll = 'ALL')

    and (LOB in (Select * from SplitList(',',@LOB) as ListItem) or @LOB = 'ALL')

    and (Groupid in (Select * from SplitList(',',@GroupList) as ListItem) or @Grouplist = 'ALL')

    and paydate between @From and @TO

    order by Slastname, sFirstname, claimid, linenum
  • How to use a parameter to return all records
Answer this question