SQL Query for a search function

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

Transact-SQL (2005)

SQL Query for a search function

Author

Topic

CSharpNewbie
Starting Member

39 Posts

Posted - 2010-06-28 : 14:54:55

Hi, I am creating a sql stored proc for a search function. I am having problems with the query, given the possibilities of null or empty strings

I would greatly appreciate if someone can help with the query:

Select RecID, VendorNum, VendorName, InvoiceNum, ItemNum, (SELECT CONVERT(VARCHAR(11), DateOfRecord, 106) AS [DD MON YYYY]) As DateOfRecord, NeedsUpdate, RecAddUserID From tbInvoiceLog

WHERE
If @ItemNum is Not Null, then
(CASE @SearchFilter
WHEN 0 THEN @ItemNum + '%'
WHEN 1 THEN '%' + @ItemNum + '%'
WHEN 2 THEN '%' + @ItemNum
END)

AND if @VendorNum is Not Null, then VendorNum = @VendorNum
AND if @RecSearchUserID i Not Nulll, then RecSearcherID = @RecSearchID
AND DateOfRecord BETWEEN @StartDate AND @EndDate

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-28 : 15:56:51

Where's the equality?

What do you want to make @ItemNum equal to?

Let's say Col1


WHERE Col1 LIKE CASE
				WHEN @SearchFilter = 0 THEN       @ItemNum + '%'
				WHEN @SearchFilter = 0 THEN '%' + @ItemNum + '%'
				WHEN @SearchFilter = 0 THEN '%' + @ItemNum
--									   ELSE       @ItemNum
				END
  AND VendorNum		= COALESCE(@VendorNum, VendorNum)
  AND RecSearcherID = COALESCE(@RecSearchUserID, RecSearcherID)
  AND DateOfRecord	BETWEEN @StartDate AND @EndDate

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

CSharpNewbie
Starting Member

39 Posts

Posted - 2010-06-29 : 07:35:29

Brett, this is in a where clause
ItemNUm = @ItemNum, depending on the value of the SearchFilter

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-29 : 11:20:41

Sounds like you are getting into catch-all queries. Check out this link:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-29 : 14:05:18

quote:
Originally posted by CSharpNewbie

Brett, this is in a where clause
ItemNUm = @ItemNum, depending on the value of the SearchFilter

Huh?

You mean you are letting them eneter

Color = 'Blue'?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources