Store procedure checking for null values

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)

Store procedure checking for null values

Author

Topic

PoseyRobert
Starting Member

27 Posts

Posted - 2014-08-04 : 12:49:22

I have a store procedure that I am having problems with when no data is being added to 1 of the parameters.

If the store procedure has data in all the parameters I get the correct data.

However if I decide not to pass data in one of the parameters the store procedure no longer works.

How do I get the information is bold to do an if condition.

declare @engrStatusCode varchar(30)
declare @facilityCode varchar(30)
declare @inventoryCode varchar(30)

SET @engrStatusCode = 'A'
SET @facilityCode = '04'
SET @inventoryCode = 'FG'

if @engrStatusCode = '' or UPPER(@engrStatusCode) = 'ALL'
set @engrStatusCode = null

if @facilityCode = '' or UPPER(@facilityCode) = 'ALL'
set @facilityCode = null

if @itemCodeStart = '' or UPPER(@itemCodeStart) = 'ALL'
set @itemCodeStart = null

select imil.Item_Code, im.Item_Desc, imil.Facility_Code, im.Engr_Status_Code, im.Class_Data_Code, im.Um_Code

from ITEMS_IPLS_LOCATIONS as imil
left outer join ITEMS as im on im.Item_Code = imil.Item_Code and im.Facility_Code = imil.Facility_Code
left outer join ITEMS_IPLS as ipls on ipls.Item_Code = imil.Item_Code and ipls.Facility_Code = imil.Facility_Code
WHERE imil.Location_Code NOT IN ('ALARMSW','COMPLNT')

if @engrStatusCode is not null

AND

@engrStatusCode is not null
im.Engr_Status_Code IN
(Select value from dbo.fn_convertCommaValueToTable(@engrStatusCode, ',') )

if @facilityCode is not null

AND

@facilityCode is not null
imil.Facility_Code IN
(Select value from dbo.fn_convertCommaValueToTable(@facilityCode, ',') )

if @inventoryCode is not null

AND

@inventoryCode is not null
im.Inventory_Type_Code IN
(Select value from dbo.fn_convertCommaValueToTable(@inventoryCode, ',') )

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-04 : 12:53:09

You can use CASE.

AND
CASE WHEN @engrStatusCode is not null THEN ... ELSE ... END

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

PoseyRobert
Starting Member

27 Posts

Posted - 2014-08-04 : 13:00:12

Hi tkizer,

I tried using the CASE. But it onlong check 1 condition not all.

WHERE imil.Location_Code NOT IN ('ALARMSW','COMPLNT') AND

1 = (CASE
WHEN @engrStatusCode is not null AND
im.Engr_Status_Code IN
(Select value from dbo.fn_convertCommaValueToTable(@engrStatusCode, ',') ) THEN 1
WHEN @facilityCode is not null AND
imil.Facility_Code IN
(Select value from dbo.fn_convertCommaValueToTable(@facilityCode, ',') ) THEN 1
WHEN @inventoryCode is not null AND
im.Inventory_Type_Code IN
(Select value from dbo.fn_convertCommaValueToTable(@inventoryCode, ',') ) THEN 1
WHEN @engrStatusCode is null and @facilityCode is null and @inventoryCode is null and @itemCodeStart is null and @itemCodeEnd is null and
imil.Facility_Code is not null THEN 1
ELSE 0
END)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-04 : 13:28:29

Why do you have this: 1 = (CASE? Remove "1 = "

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-04 : 13:48:18

It seems like you are trying to use optional parameters: The following technique might work for you.

I guessed at simulating your function

CREATE TABLE X
(ID int IDENTITY(1,1) ,Color varchar(10))

INSERT INTO X
VALUES('RED'),('BLUE'),('GREEN'),('YELLOW'),('ORANGE'),('RED'),('BLUE'),('GREEN'),('YELLOW'),('ORANGE')

CREATE FUNCTION dbo.ImitFunxtion(@ColorList varchar(50))
RETURNS @R TABLE (COLORS VARCHAR(10))
AS
BEGIN
IF @ColorList IS NOT NULL
BEGIN
INSERT INTO @R
SELECT 'RED' UNION
SELECT 'GREEN' UNION
SELECT 'YELLOW'
END

RETURN

END

DECLARE @ID INT , @Color varchar(10)

SET @ID = 2

SELECT *FROM X
WHERE (ID= @ID or @ID IS NULL )
AND (Color= @Color or @Color IS NULL )

SET @Color = 'Red'
SET @ID = NULL

SELECT *FROM X
WHERE (ID= @ID or @ID IS NULL )
AND (Color= @Color or @Color IS NULL )

SET @Color = NULL
SET @ID = NULL

SELECT *FROM X
WHERE (ID= @ID or @ID IS NULL )
AND (Color= @Color or @Color IS NULL )

-- WITH FUNCTION
DECLARE @IDf INT , @Colorf varchar(10)

SELECT * FROM X
WHERE (ID= @IDf or @IDf IS NULL )
AND (Color IN (SELECT * FROM dbo.ImitFunxtion(@Colorf)) or @Colorf IS NULL )

-- WITH FUNCTION
SET @Colorf = 'BLUE'
SET @IDf = NULL

SELECT * FROM X
WHERE (ID= @IDf or @IDf IS NULL )
AND (Color IN (SELECT * FROM dbo.ImitFunxtion(@Colorf)) or @Colorf IS NULL )

PoseyRobert
Starting Member

27 Posts

Posted - 2014-08-04 : 15:07:55

Thanks MichaelJSQL,

It's working now.

Thanks very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources