Null Variables in SP - 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 2000 Forums

SQL Server Development (2000)

Null Variables in SP

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-04-11 : 13:17:01

I have the following procedure I wish to change so that if I supply a null for one or more of my @ conditions that it will return all rows for that Condition.

I.E. If I on enter a value in length and the other 2 are null it will only filter by length giving me all widths and depths for that length or if I enter width and Depth it will give all lengths for the supplied width and Depth ext.

I tried using IsNull but I am new to SQL and must be messing up the syntax or placement.

CREATE PROCEDURE dbo.zzzzzDieexp(@Length float,
@width float,
@depth float)

AS SELECT dbo.Dies.*, dbo.Sub_die.Piece_ID, dbo.Sub_die.Piece_Up, dbo.Sub_die.CAD_ID, dbo.Sub_die.C_Length, dbo.Sub_die.C_Width, dbo.Sub_die.C_Depth,
dbo.Sub_die.C_Style, dbo.Sub_die.Addition_Date AS Expr1, dbo.Sub_die.Glue_Cat
FROM dbo.Dies INNER JOIN
dbo.Sub_die ON dbo.Dies.Die_ID = dbo.Sub_die.Die_ID
WHERE (dbo.Sub_die.C_Length = @Length) AND (dbo.Sub_die.C_Width = @width) AND (dbo.Sub_die.C_Depth = @depth)
ORDER BY dbo.Sub_die.C_Length, dbo.Sub_die.C_Width, dbo.Sub_die.C_Depth, dbo.Sub_die.C_Style, dbo.Sub_die.Piece_Up
GO

Thanks in Advance

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-11 : 13:28:07

Funny, I never thought to use LIKE with numbers but it seems to work just fine! Any caveats to this approach?

CREATE PROCEDURE dbo.zzzzzDieexp(@Length float,
@width float,
@depth float)

AS SELECT dbo.Dies.*, dbo.Sub_die.Piece_ID, dbo.Sub_die.Piece_Up, dbo.Sub_die.CAD_ID, dbo.Sub_die.C_Length, dbo.Sub_die.C_Width, dbo.Sub_die.C_Depth,
dbo.Sub_die.C_Style, dbo.Sub_die.Addition_Date AS Expr1, dbo.Sub_die.Glue_Cat
FROM dbo.Dies INNER JOIN
dbo.Sub_die ON dbo.Dies.Die_ID = dbo.Sub_die.Die_ID

WHERE (dbo.Sub_die.C_Length LIKE isnull(@Length,'%')) AND
(dbo.Sub_die.C_Width LIKE isnull(@width,'%')) AND
(dbo.Sub_die.C_Depth like isnull(@depth,'%'))

ORDER BY dbo.Sub_die.C_Length, dbo.Sub_die.C_Width, dbo.Sub_die.C_Depth, dbo.Sub_die.C_Style, dbo.Sub_die.Piece_Up

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-11 : 13:29:58

by the way -- if you can, stay away from Floats -- they are not exact datatypes. Consider using decimal() or Numeric() instead with as many decimals of accuracy as you need.

Only use floats if possible values can be extremely large AND extremely small in the same field.

- Jeff

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-04-11 : 13:35:17

quote:

WHERE (dbo.Sub_die.C_Length LIKE isnull(@Length,'%')) AND
(dbo.Sub_die.C_Width LIKE isnull(@width,'%')) AND
(dbo.Sub_die.C_Depth like isnull(@depth,'%'))
- Jeff

Returns Error

Error Converting data type varchar to float.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-04-11 : 13:38:17

quote:

by the way -- if you can, stay away from Floats -- they are not exact datatypes. Consider using decimal() or Numeric() instead with as many decimals of accuracy as you need.

Only use floats if possible values can be extremely large AND extremely small in the same field.

- Jeff

I only need 4 to the left and 6 to the right of the decimal

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-11 : 13:41:46

Yep ... the last statement below indeed returns an error:

declare @a float
declare @b float
declare @c float
declare @d float

set @a = 23.2323
set @b = 23.2323
set @c = 1232
set @d = null

select case when @a like isnull(@b,'%') then 1 else 0 end

select case when @a like isnull(@c,'%') then 1 else 0 end

select case when @a like isnull(@d,'%') then 1 else 0 end

funny ... it almost works but not quite ... not sure why.

Until I thought LIKE would work, I was going to suggest the more traditional approach so here it is:

WHERE (@Length is null OR dbo.Sub_die.C_Length = @Length) AND
(@width is Null or dbo.Sub_die.C_Width = @width) AND
(@Depth is null OR dbo.Sub_die.C_Depth = @depth)

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-11 : 13:43:51

If you need 4 to the left and 6 to the right, consider using:

numeric(10,4) or decimal(10,4)

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-11 : 13:47:56

quote:

WHERE (dbo.Sub_die.C_Length LIKE isnull(@Length,'%')) AND
(dbo.Sub_die.C_Width LIKE isnull(@width,'%')) AND
(dbo.Sub_die.C_Depth like isnull(@depth,'%'))

ORDER BY dbo.Sub_die.C_Length, dbo.Sub_die.C_Width, dbo.Sub_die.C_Depth, dbo.Sub_die.C_Style, dbo.Sub_die.Piece_Up

Not if you end up with null values you won't. Think you have to do:

WHERE (Convert(varchar(20),dbo.Sub_die.C_Length) LIKE isnull(Convert(varchar(20),@Length),'%')) AND
(Convert(varchar(20),dbo.Sub_die.C_Width) LIKE isnull(Convert(varchar(20),@width),'%')) AND
(Convert(varchar(20),dbo.Sub_die.C_Depth) like isnull(Convert(varchar(20),@depth),'%'))

Let me know if that works.

Thanks.

EDIT: Had to change the location of the parenthesis

Brett

8-)

Edited by - x002548 on 04/11/2003 13:49:48

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-04-11 : 13:49:17

Thanks Jeff that works Great !!!!!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-11 : 13:49:48

Brett -- you want to avoid doing conversions like that because SQL can then no longer use indexes.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-11 : 13:50:40

Good Point! Stage 2 predicate and all (is that sargable or nonsargable?)

Brett

8-)

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-04-11 : 14:01:07

Actually when I used Access GUI it converted it to this

WHERE (@Length IS NULL) AND (@width IS NULL) AND (@Depth IS NULL) OR
(@Length IS NULL) AND (@Depth IS NULL) AND (dbo.Sub_die.C_Width = @width) OR
(@width IS NULL) AND (@Depth IS NULL) AND (dbo.Sub_die.C_Length = @Length) OR
(@Depth IS NULL) AND (dbo.Sub_die.C_Width = @width) AND (dbo.Sub_die.C_Length = @Length) OR
(@Length IS NULL) AND (@width IS NULL) AND (dbo.Sub_die.C_Depth = @Depth) OR
(@Length IS NULL) AND (dbo.Sub_die.C_Width = @width) AND (dbo.Sub_die.C_Depth = @Depth) OR
(@width IS NULL) AND (dbo.Sub_die.C_Length = @Length) AND (dbo.Sub_die.C_Depth = @Depth) OR
(dbo.Sub_die.C_Width = @width) AND (dbo.Sub_die.C_Length = @Length) AND (dbo.Sub_die.C_Depth = @Depth)

Access SP GUI sometimes makes a mess of things trying to be helpfull!!

Edited by - JimL on 04/11/2003 14:05:51

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-11 : 14:04:16

Don't let the Access GUI convert things like that ... I am not sure, it may get optimized exactly the same way, but it certainly *looks* less efficient !

(harder to read and understand as well)

The Access GUI doesn't really convert OR's very well sometimes...

- Jeff

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-04-11 : 14:07:24

I think your very Right Jeff

Thanks Again for the Help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources