Search stored procedure - 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 2008 Forums

Transact-SQL (2008)

Search stored procedure

Author

Topic

becky.b
Starting Member

4 Posts

Posted - 2011-02-06 : 07:28:15

HI,

I have created a stored procedure for search.
This is the stored procedure code:

ALTER procedure [OS].[GET_ItemsRecords]
@itemID varchar (10) =  NULL,
@itemName varchar (100) =  NULL
AS 
select * from OS.Items where 
itemID  = @itemID  AND
itemName = @itemName;

I try to search using the itemID and it does not shows anything. The database only retrive the records when I enter in the item id and item name. How do i modify the search sql such that when I search for either the item id or item name, it displays the records. Also, if I enter the matching ID and Name, record will be displayed, else it will not be displayed. Thanks.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-06 : 07:50:12

Change the AND condition to an OR

quote:

How do i modify the search sql such that when I search for either the item id or item name, it displays the records.

SELECT * FROM OS.Items
Where itemID = @itemID OR itename = @itemname

quote:

Also, if I enter the matching ID and Name, record will be displayed, else it will not be displayed

Not sure I understand...are you saying you only want it to return records if you enter both?

If you enter a matching itemID and name, the record will be returned. If there are no matches to what you passed in as @itemid and @itemname, nothing will be returned.

Poor planning on your part does not constitute an emergency on my part.

becky.b
Starting Member

4 Posts

Posted - 2011-02-06 : 12:34:57

Nope, I want user to search not only by matching records but also individually. Like they can search by 3 ways:
Template ID
Template Name
Template ID + Template Name.

Thks:)

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-06 : 12:46:18

So this?

[code]
ALTER procedure [OS].[GET_ItemsRecords]
@itemID varchar (10) = NULL,
@itemName varchar (100) = NULL
AS
select DISTINCT *
from OS.Items
where
(itemID = @itemID AND
itemName = @itemName)

OR
itemID = @itemid
OR
itemname = @itemname

[code]

I would also specify the columns of the target table you are selecting from, it is not only faster, but it will prevent any issues with the procedure if you add columns to the table later.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources