search table with multiple input using stored proc

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

Transact-SQL (2000)

search table with multiple input using stored proc

Author

Topic

vemulavijay
Starting Member

32 Posts

Posted - 2009-06-03 : 09:11:06

Hi,

I am creating a stored procedure for searching table DealTable.

This table contains columns: Customer, DealID, DealCreatedDate, DealDescription, DealVersionID, OpportunityID.

The user can enter any information to search DealTable in my UI. some input parameters can be null.

I am unable to create a stored procedure to implement this search feature.

Please note that all information is present in only one table. I have to just search the DealTable.

Pls help if somebody has logic for this. Thanks in Advance.

My Stored Procedure:

CREATE PROCEDURE DBO.SEARCH_DEAL
(
@Customer VARCHAR,
@Deal_ID INT,
@DEAL_CREATED_DATE DATETIME,
@DEAL_DESCRIPTION VARCHAR,
@DEAL_VERSION_ID INT
@OPPORTUNITY_ID INT,
)
AS
BEGIN

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-03 : 09:17:05

Do like this

CREATE PROCEDURE DBO.SEARCH_DEAL
(
@Customer VARCHAR,
@Deal_ID INT,
@DEAL_CREATED_DATE DATETIME,
@DEAL_DESCRIPTION VARCHAR,
@DEAL_VERSION_ID INT
@OPPORTUNITY_ID INT,
)
AS
BEGIN
Select columns from yourtable
where
(Customer=@Customer or @Customer is null)
and
(Deal_ID=@Deal_ID or @Deal_ID is null)
.
.

Madhivanan

Failing to plan is Planning to fail