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 2008 Forums

Transact-SQL (2008)

search table with multiple input using stored proc

Author

Topic

vemulavijay
Starting Member

32 Posts

Posted - 2009-06-03 : 09:15:24

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-03 : 09:21:42

An approach:
WHERE (@Customer is null OR Customer = @Customer)
and (@Deal_ID is null OR Deal_ID = @Deal_ID)
and ...

No, you're never too old to Yak'n'Roll if you're too young to die.

SQLRatankalwa
Starting Member

42 Posts

Posted - 2009-06-03 : 11:25:12

You can Set the Customer

Set @Customer = '%' + @Customer + '%'

You can do the same thing for the @Deal_Id by using a Variable

Declare @Sdeal_Id Varchar(32)
If IsNull(@DealId, 0) = 0 Set @Sdeal_Id = '%' Else Set @Sdeal_id = '%' + Cast(@Deal_ID as Varchar) + '%'

WHERE (@Customer is null OR Customer Like @Customer)
and (@Deal_ID is null OR Deal_ID Like @SDeal_ID)
and ...

i hope it helps.

Ratan Kalwa
SQL Server Professionals User Group

http://www.revalsys.com

vemulavijay
Starting Member

32 Posts

Posted - 2009-06-04 : 00:56:01

Thanks for the suggestion. i will try and get back.

vemulavijay
Starting Member

32 Posts

Posted - 2009-06-04 : 02:14:42

This works Great.

Thanks Ratan Kalwa and webfred

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources