Stored Procedure... If statements

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)

Stored Procedure... If statements

Author

Topic

vbjohn
Starting Member

32 Posts

Posted - 2003-04-17 : 12:43:28

Why does this not work? Any suggestions?

CREATE PROCEDURE dbo.PayList @PCode Char(6) = Null, @PType Char(10) = 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 @PCode <> NULL begin
SET @ENDSTATEMENT = @ENDSTATEMENT & " AND UPR00400.PAYRCORD = @PCode"
END

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

John-

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-04-17 : 12:47:28

Because you WHERE clause is bad.

Scrap the variables ..


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

SELECT 
UPR00100.EMPLOYID, UPR00100.LASTNAME, UPR00100.FRSTNAME, UPR00100.MIDLNAME, 
UPR00400.PAYRCORD, UPR00400.PAYRTAMT, UPR00400.RPTASWGS, UPR00400.SBJTFDTX 
FROM 
UPR00100 INNER JOIN UPR00400 ON 
UPR00100.EMPLOYID = UPR00400.EMPLOYID 
WHERE 
UPR00100.INACTIVE = 0 and
UPR00400.PAYTYPE = coalesce(@PType, UPR00400.PAYTYPE) and
UPR00400.PAYRCORD = coalesce(@PCode, UPR00400.PAYRCORD)
ORDER BY 
UPR00100.EMPLOYID ASC 
GO

Jay White
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-18 : 11:33:42

FYI -- you'll get better performance with:

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

SELECT
UPR00100.EMPLOYID, UPR00100.LASTNAME, UPR00100.FRSTNAME, UPR00100.MIDLNAME,
UPR00400.PAYRCORD, UPR00400.PAYRTAMT, UPR00400.RPTASWGS, UPR00400.SBJTFDTX
FROM
UPR00100 INNER JOIN UPR00400 ON
UPR00100.EMPLOYID = UPR00400.EMPLOYID
WHERE
UPR00100.INACTIVE = 0 and
UPR00400.PAYTYPE LIKE coalesce(@PType, '%') and
UPR00400.PAYRCORD LIKE coalesce(@PCode, '%')
ORDER BY
UPR00100.EMPLOYID ASC

Because now the WHERE criteria on the right hand side is a constant, and does not have to be re-evaluated for each row in the table.

(and, of course, i prefer ISNULL() -- easier to spell !!!!

... COALESCE seems so "snobby" to use ! )

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources