If Statements..... - 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)

If Statements.....

Author

Topic

vbjohn
Starting Member

32 Posts

Posted - 2003-04-22 : 12:44:51

Is there a way that I can make this work?

CREATE PROCEDURE dbo.PayList @PCode Char(6) = Null, @MemNum Char(6) = Null AS

DECLARE @ENDSTATEMENT AS VARCHAR(200)

SET @ENDSTATEMENT= " UPR00100.INACTIVE = 0"

IF @PType <> Null begin
SET @ENDSTATEMENT = @ENDSTATEMENT & " AND UPR00400.PAYTYPE = @PType"
END
IF @MemNum <> Null begin
SET @ENDSTATEMENT = @ENDSTATEMENT & " AND UPR00100.EMPLOYID = @MemNum"
END

SELECT
UPR00100.EMPLOYID,UPR00100.LASTNAME, UPR00100.FRSTNAME, UPR00100.MIDLNAME, UPR00100.JOBTITLE,
UPR00400.PAYRCORD, UPR00400.PAYRTAMT FROM
UPR00100 INNER JOIN UPR00400 ON
UPR00100.EMPLOYID = UPR00400.EMPLOYID
WHERE
@ENDSTATEMENT
ORDER BY
UPR00100.EMPLOYID ASC

GO

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-22 : 12:48:29

IF @PType is not Null, + instead of &, ' instetad bof " for strings, dynamic sql to execute

or

CREATE PROCEDURE dbo.PayList
@PCode Char(6) = Null,
@MemNum Char(6) = Null
AS

SELECT
UPR00100.EMPLOYID,UPR00100.LASTNAME, UPR00100.FRSTNAME, UPR00100.MIDLNAME, UPR00100.JOBTITLE,
UPR00400.PAYRCORD, UPR00400.PAYRTAMT
FROM
UPR00100
INNER JOIN UPR00400 ON
UPR00100.EMPLOYID = UPR00400.EMPLOYID
WHERE UPR00100.EMPLOYID = coalesce(@MemNum, UPR00100.EMPLOYID)
and UPR00400.PAYTYPE = coalesce(@PType, UPR00400.PAYTYPE)
ORDER BY UPR00100.EMPLOYID ASC

go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 04/22/2003 12:53:44

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-22 : 12:56:09

Well, I think you've got your VB and TRANSACT SQL mixed up.

Either way, you could do dynamic SQL, which I think is not the best or:

EDIT: Nigel, I think I had a brain cramp. Thanks.

IF @PType IS NOT NULL AND @MemNum IS NOT NULL
SELECT UPR00100.EMPLOYID,UPR00100.LASTNAME, UPR00100.FRSTNAME, UPR00100.MIDLNAME, UPR00100.JOBTITLE,
UPR00400.PAYRCORD, UPR00400.PAYRTAMT
FROM UPR00100
INNER JOIN UPR00400
ON UPR00100.EMPLOYID = UPR00400.EMPLOYID
WHERE UPR00100.INACTIVE = 0
AND UPR00400.PAYTYPE = @PType AND UPR00100.EMPLOYID = @MemNum
ORDER BY UPR00100.EMPLOYID ASC

IF @PType IS NOT NULL AND @MemNum Is Null
SELECT UPR00100.EMPLOYID,UPR00100.LASTNAME, UPR00100.FRSTNAME, UPR00100.MIDLNAME, UPR00100.JOBTITLE,
UPR00400.PAYRCORD, UPR00400.PAYRTAMT
FROM UPR00100
INNER JOIN UPR00400
ON UPR00100.EMPLOYID = UPR00400.EMPLOYID
WHERE UPR00100.INACTIVE = 0
AND UPR00400.PAYTYPE = @PType
ORDER BY UPR00100.EMPLOYID ASC

IF @PType IS Null AND @MemNum IS NOT NULL
SELECT UPR00100.EMPLOYID,UPR00100.LASTNAME, UPR00100.FRSTNAME, UPR00100.MIDLNAME, UPR00100.JOBTITLE,
UPR00400.PAYRCORD, UPR00400.PAYRTAMT
FROM UPR00100
INNER JOIN UPR00400
ON UPR00100.EMPLOYID = UPR00400.EMPLOYID
WHERE UPR00100.INACTIVE = 0
AND UPR00100.EMPLOYID = @MemNum
ORDER BY UPR00100.EMPLOYID ASC

Brett

8-)

Edited by - x002548 on 04/22/2003 12:57:34

Edited by - x002548 on 04/22/2003 13:06:18

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-22 : 13:04:12

Removed to prevent embarrassment.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 04/22/2003 13:09:24

vbjohn
Starting Member

32 Posts

Posted - 2003-04-22 : 14:24:30

You see... there is a form in Visual Basic that I programm. Which the end user can select different criteria for their reports. So it can come out in various outcomes. Like a total of 16 different outputs.

I got the error around this area......

WHERE
@ENDSTATEMENT
ORDER BY

John-

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-22 : 14:25:47

Do you try the responses?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vbjohn
Starting Member

32 Posts

Posted - 2003-04-22 : 14:38:45

what responses?

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-22 : 15:05:48

For instance

CREATE PROCEDURE dbo.PayList
@PCode Char(6) = Null,
@MemNum Char(6) = Null
AS

SELECT
UPR00100.EMPLOYID,UPR00100.LASTNAME, UPR00100.FRSTNAME, UPR00100.MIDLNAME, UPR00100.JOBTITLE,
UPR00400.PAYRCORD, UPR00400.PAYRTAMT
FROM
UPR00100
INNER JOIN UPR00400 ON
UPR00100.EMPLOYID = UPR00400.EMPLOYID
WHERE UPR00100.EMPLOYID = coalesce(@MemNum, UPR00100.EMPLOYID)
and UPR00400.PAYTYPE = coalesce(@PType, UPR00400.PAYTYPE)
ORDER BY UPR00100.EMPLOYID ASC

go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vbjohn
Starting Member

32 Posts

Posted - 2003-04-22 : 15:19:49

That would not work since, if @Ptype is blank then it should not be in the WHERE clause.

John-

afterburn
Starting Member

28 Posts

Posted - 2003-04-22 : 15:29:05

Well if you are going to do that type of dynamic sql inside of a stored procedure I beleive that it is only possible like
Declare @Query varchar(8000)

set @Query = 'Select * from table'
if @PType is not null
begin
Set @Query = @Query + ' where PType = ' + Convert(varchar(10),@Ptype)'
end

exec sp_executesql @Query

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-22 : 15:35:09

I *almost* don't want to get involved, but how about

If LEN(@Ptype) = 0 -- blanks will yield zero
SET @Ptype = NULL

proceed with nr's select.

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-22 : 15:47:24

quote:

For instance

CREATE PROCEDURE dbo.PayList
@PCode Char(6) = Null,
@MemNum Char(6) = Null
AS

SELECT
UPR00100.EMPLOYID,UPR00100.LASTNAME, UPR00100.FRSTNAME, UPR00100.MIDLNAME, UPR00100.JOBTITLE,
UPR00400.PAYRCORD, UPR00400.PAYRTAMT
FROM
UPR00100
INNER JOIN UPR00400 ON
UPR00100.EMPLOYID = UPR00400.EMPLOYID
WHERE UPR00100.EMPLOYID = coalesce(@MemNum, UPR00100.EMPLOYID)
and UPR00400.PAYTYPE = coalesce(@PType, UPR00400.PAYTYPE)
ORDER BY UPR00100.EMPLOYID ASC

go

Yes it will! Did you look up COALESCE?

If it's Null Nigels Predicate will basically say: "APPLES"="APPLES"

Which if I'm not mistaken is ALWAYS true.

NIGEL: Thanks for the Edit [sheepish_grin]Brett[/sheepish_grin]

Brett

8-)

Edited by - x002548 on 04/22/2003 15:48:12

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-22 : 16:03:30

I recommend:

CREATE PROCEDURE dbo.PayList
@PCode Char(6) = Null,
@MemNum Char(6) = Null
AS

SELECT
UPR00100.EMPLOYID,UPR00100.LASTNAME, UPR00100.FRSTNAME, UPR00100.MIDLNAME, UPR00100.JOBTITLE,
UPR00400.PAYRCORD, UPR00400.PAYRTAMT
FROM
UPR00100
INNER JOIN UPR00400 ON
UPR00100.EMPLOYID = UPR00400.EMPLOYID
WHERE UPR00100.EMPLOYID LIKE ISNULL(@MemNum, '%')
and UPR00400.PAYTYPE LIKE ISNULL(@PType,'%')
ORDER BY UPR00100.EMPLOYID ASC

That should perform better because the right-hand part of the criteria is now a constant.

If @MemNum or @Ptype being equal to '' is causing problems, then why is '' being passed as a parameter instead of Null? make sure the client passes the correct "do not search this field" value. If that is not possible, then just include these lines before the SELECT :

If @MemNum = '' SET @MemNum = Null
If @Ptype = '' SET @Ptype = Null

Keep it simple! But don't overlook LIKE -- it performs very well and handles "selective criteria" very well !

- Jeff

Edited by - jsmith8858 on 04/22/2003 16:14:11

vbjohn
Starting Member

32 Posts

Posted - 2003-04-22 : 16:37:33

thanks everyone for their help. Someone has sent me this code and it works.

CREATE PROCEDURE dbo.PayList @PCode Char(6) = Null, @MemNum Char(6) = Null, @PType Char(10) = Null, @JTitle Char(3) = Null AS
SET NOCOUNT ON
DECLARE @ENDSTATEMENT AS VARCHAR(400)

SELECT
UPR00100.EMPLOYID, UPR00100.INACTIVE, UPR00100.LASTNAME, UPR00100.FRSTNAME, UPR00100.MIDLNAME, UPR00100.JOBTITLE,
UPR00400.PAYRCORD, UPR00400.PAYRTAMT FROM
UPR00100
INNER JOIN UPR00400 ON
UPR00100.EMPLOYID = UPR00400.EMPLOYID
WHERE
UPR00100.INACTIVE = 0 AND (@PType IS NULL OR UPR00400.PAYTYPE = @PType)
AND (@MemNum IS NULL OR UPR00100.EMPLOYID = @MemNum) AND
(@PCode IS NULL OR UPR00400.PAYRCORD = @PCode)
AND (@JTitle IS NULL OR UPR00100.JOBTITLE = @JTitle)
ORDER BY UPR00100.EMPLOYID ASC

SET NOCOUNT OFF
GO

John-

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-22 : 16:53:33

What about
>> That would not work since, if @Ptype is blank then it should not be in the WHERE clause.

That's very similar to my first response

CREATE PROCEDURE dbo.PayList @PCode Char(6) = Null, @MemNum Char(6) = Null, @PType Char(10) = Null, @JTitle Char(3) = Null AS
SET NOCOUNT ON

SELECT
UPR00100.EMPLOYID, UPR00100.INACTIVE, UPR00100.LASTNAME, UPR00100.FRSTNAME, UPR00100.MIDLNAME, UPR00100.JOBTITLE,
UPR00400.PAYRCORD, UPR00400.PAYRTAMT FROM
UPR00100
INNER JOIN UPR00400 ON
UPR00100.EMPLOYID = UPR00400.EMPLOYID
WHERE
UPR00100.INACTIVE = 0
AND coalesce(@PType,UPR00400.PAYTYPE) = UPR00400.PAYTYPE
AND coalesce(@MemNum, UPR00100.EMPLOYID) = UPR00100.EMPLOYID
AND coalesce(@PCode, UPR00400.PAYRCORD) = UPR00400.PAYRCORD
AND coalesce(@JTitle, UPR00100.JOBTITLE) = UPR00100.JOBTITLE
ORDER BY UPR00100.EMPLOYID ASC

SET NOCOUNT OFF
GO

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-22 : 19:43:00

Has anyone tested the performance of
(@Var IS NULL OR Table.Column = @Var)

over

Table.Column LIKE ISNULL(@Var,'%')

over

Table.Column = coalesce(@Var, Table.Column)

I usually use (@Var IS NULL OR Table.Column = @Var)
I find it easier to build more complex boolean statements.

Edited by - ValterBorges on 04/22/2003 19:43:18

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-04-22 : 21:45:48

This is, technically, the proper way to do it:

(@Var IS NULL OR Table.Column = @Var)

Because the IsNull() and Coalesce() versions do not handle null COLUMNS properly, only null variables (you'd have to SET ANSI_NULLS ON, and even then that's not really the proper way) Although I always use IsNull(@var, column) myself, because I never have to search nullable columns anyway, and I'm a lazy typist.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-22 : 22:52:00

I 100% agree, the "(Not A) or B" expression is definitely the most logically sound way to do it, and is the exact boolean equivalent of

"A -> B"

or

"If A, then B"

which we all remember from our "Logic 101" days ! And this is technically the logic we wish to follow in the select clause (if a value is selected, then field must be equal to that value).

I have found that the LIKE operator tends to work more efficiently, however, because SQL doesn't always optimize well with OR's. But you're right, that can cause problems with Nulls (as do most comparisons, of course).

Many ways to skin a cat! If the "don't search this field" value is not Null, but rather "all" or a seperate variable value or something like that, then it is definitely good to know the (Not a) OR B method because ISNULL() or coalsece() don't help you much in that situation.

For example,

(@FilterResults = 0 OR (Field1 = @Field1Filter AND Field2 = @Field2Filter))

which is the equivalent of

"If @FitlerResults <> 0, then Field1 must equal @Field1Filter and Field2 must equal @Field2Filter".

- Jeff

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-04-23 : 08:14:35

quote:
Many ways to skin a cat!

etc.
...freaking guys. Championing techniques that confuse me!
After embracing COALESCE there is now a "MO' BETTER"
equivalent! When will the madness end... JK

Actually I'm just marking this.

Voted best SQL forum nickname...."Tutorial-D"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources