Dynamic Where Clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic Where Clause

Author

Topic

janbuckley
Starting Member

1 Post

Posted - 2001-04-12 : 13:12:36

I'm new to stored procedures. I read the article by Garth Wells on dynamic where clauses and am trying to implement it in the following situation. I have a search form in asp that users can search orders by number, company, and status. Only users that are part of the 'Rep' group have access to this page and all orders can be anything other than in 'Starting' status.

The following procedure is allowing me to query a view, vwQueue, based on order numbers, but the other two search criteria aren't working.

Any help would be appreciated. Janel

CREATE PROCEDURE sp_Test
(
@UserGroup varchar(15),
@UserName varchar(30),
@OnLineOrder_Key int = NULL,
@CompanyName varchar(50) = NULL,
@Status varchar(20)= NULL
)
AS
SELECT *
FROM vwQueue
WHERE (@UserGroup = 'Rep' AND Status != 'Starting') AND
OnLineOrder_Key = COALESCE(@OnLineOrder_Key,OnLineOrder_Key) OR
CompanyName = COALESCE(@CompanyName,CompanyName) + '%' OR
Status_Key = COALESCE(@Status,Status_Key)
ORDER BY OrderType, OnLineOrder_Key

Edited by - janbuckley on 04/12/2001 13:25:53

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources