Stroed Procedure passing a null value

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)

Stroed Procedure passing a null value

Author

Topic

feelingsheepish
Starting Member

8 Posts

Posted - 2009-04-02 : 07:48:41

Hi,

I have a Stored Procedure that passes a parameter.

If the parameter is null i would like to display all records, If it is not null then i would like it to display only associated records.

At the moment when i enter a null value, no records are returned - i would like to display all records.
Thanks

[code]ALTER PROCEDURE [dbo].[uspTextSearchItems]
@Search varchar(50)
AS

IF @Search =Null
SELECT tblCategory.Name, tblItem.ItemID, tblItem.ItemNo, tblItem.Description, tblItem.Brand, tblItem.Model, tblItem.Serial, tblItem.Status, tblItem.Memo,
tblItem.RecordedBy, tblItem.ts
FROM tblItem LEFT OUTER JOIN
tblCategory ON tblItem.CategoryID = tblCategory.CategoryID
ELSE

SELECT tblCategory.Name, tblItem.ItemID, tblItem.ItemNo, tblItem.Description, tblItem.Brand, tblItem.Model, tblItem.Serial, tblItem.Status, tblItem.Memo,
tblItem.RecordedBy, tblItem.ts
FROM tblItem LEFT OUTER JOIN
tblCategory ON tblItem.CategoryID = tblCategory.CategoryID
WHERE (tblCategory.Name LIKE '%' + @Search + '%') OR
(tblItem.ItemNo LIKE '%' + @Search + '%') OR
(tblItem.Description LIKE '%' + @Search + '%') OR
(tblItem.Brand LIKE '%' + @Search + '%') OR
(tblItem.Model LIKE '%' + @Search + '%') OR
(tblItem.Serial LIKE '%' + @Search + '%')code]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-02 : 07:52:11

IF @Search is Null

feelingsheepish
Starting Member

8 Posts

Posted - 2009-04-02 : 08:29:43

ive changed it to @Search is Null but still get same.

If i specify @Search to pass a null value - i get no records returned but get no error.

If i leave the field blank i get the following:

"Msg 201, Level 16, State 4, Procedure uspSearchItems, Line 0
Procedure or function 'uspSearchItems' expects parameter '@Search', which was not supplied.

(1 row(s) affected)"

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-02 : 08:39:51

Try this

[code]ALTER PROCEDURE [dbo].[uspTextSearchItems]
@Search varchar(50)=NULL

And then execute without passing any parameter for the proc
i.e
Exec uspSearchItems

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-02 : 08:43:31

u have to exec uspSearchItems NULL

or u have to
ALTER PROCEDURE [dbo].[uspTextSearchItems]
@Search varchar(50) = NULL
AS

u have declare parameter value without null and u exec uspSearchItems so that ur getting that error

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-04-02 : 09:13:18

You can use a filter bit in where clause

CREATE PROCEDURE [dbo].[uspTextSearchItems]
@Search varchar(50)
AS
declare @SearchFilter BIT
--set default value
set @SearchFilter =1
--check if @Search filled and reset filter bit
IF @Search is null
BEGIN
set @SearchFilter = 0
END

SELECT tblCategory.Name, tblItem.ItemID, tblItem.ItemNo, tblItem.Description, tblItem.Brand, tblItem.Model, tblItem.Serial, tblItem.Status, tblItem.Memo,
tblItem.RecordedBy, tblItem.ts
FROM tblItem LEFT OUTER JOIN
tblCategory ON tblItem.CategoryID = tblCategory.CategoryID

WHERE
--if filter is null ignore
(@SearchFilter = 0 or (tblCategory.Name LIKE '%' + @Search + '%') OR
(tblItem.ItemNo LIKE '%' + @Search + '%') OR
(tblItem.Description LIKE '%' + @Search + '%') OR
(tblItem.Brand LIKE '%' + @Search + '%') OR
(tblItem.Model LIKE '%' + @Search + '%') OR
(tblItem.Serial LIKE '%' + @Search + '%'))

feelingsheepish
Starting Member

8 Posts

Posted - 2009-04-08 : 05:35:01

hey, many thanks.

ive resolved it now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources