search query - 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

General SQL Server Forums

New to SQL Server Programming

search query

Author

Topic

sparrow37
Posting Yak Master

148 Posts

Posted - 2010-10-23 : 11:55:38

Hi All:

I am creating a query for search page. Where I want to return results based on parameters.

SELECT * FROM gbip_corporate WHERE corporate_Code=@corporateCode AND corporate_name=@Corporatename AND corporate_contactid=@CorporateContactId AND corporate_status=@CorporateStatus

It is not working when @corporateCode is '' ( empty) or @Corporatename is empty. What to change in above query.

Regards,
Asif Hameed

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-23 : 14:22:22

Is this you are looking for :

SELECT * FROM gbip_corporate
WHERE corporate_Code= @corporateCode
AND corporate_name= Case when @Corporatename is null Or @Corporatename='' Then corporate_name else @Corporatename end AND
corporate_contactid=@CorporateContactId
AND corporate_status=@CorporateStatus

Regards,
Bohra

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2010-10-25 : 04:56:10

try this [CODE]SELECT *
FROM gbip_corporate
WHERE (ISNULL(@corporateCode, '') = '' OR corporate_Code= @corporateCode)
AND (ISNULL(@Corporatename, '') = '' OR corporate_name = @Corporatename)
AND corporate_contactid = @CorporateContactId [/CODE]

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources