Stored Procedures. Help. - 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)

Stored Procedures. Help.

Author

Topic

vbjohn
Starting Member

32 Posts

Posted - 2003-04-09 : 15:14:01

Can someone look at this Stored Procedure and let me know what is wrong with this. If you have any suggestions on how to make it better, I am open.

==================================================================

CREATE PROCEDURE dbo.MPayList @PCode Char(6) = Null, @MemNum Char(6) = Null, @PType Char(10) = Null, @JTitle Char(3) = Null AS

DECLARE @ENDSTATEMENT AS VARCHAR(200)

SET @ENDSTATEMENT= " UPR00100.INACTIVE = 0"

IF @PType <> '0' begin
SET @ENDSTATEMENT = " AND UPR00400.PAYTYPE = @PType"
END
IF @PCode = '0' begin
SET @ENDSTATEMENT = " AND UPR00400.PAYRCORD = @PCode"
END
IF @MemNum = '0' begin
SET @ENDSTATEMENT = " AND UPR00100.EMPLOYID = @MemNum"
END
IF @JTitle = '0' begin
SET @ENDSTATEMENT = " AND UPR00100..JOBTITLE = @JTitle"
END

SELECT
UPR00100.EMPLOYID, UPR00400.PAYRCORD, UPR00400.PAYPEROD
FROM
UPR00100 INNER JOIN UPR00400 ON
UPR00100.EMPLOYID = UPR00400.EMPLOYID
WHERE
@ENDSTATEMENT
ORDER BY
UPR00100.EMPLOYID ASC

GO

=================================================================

John-

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-09 : 15:17:38

SELECT
UPR00100.EMPLOYID, UPR00400.PAYRCORD, UPR00400.PAYPEROD
FROM
UPR00100 INNER JOIN UPR00400 ON
UPR00100.EMPLOYID = UPR00400.EMPLOYID
WHERE UPR00100.INACTIVE = 0
and (UPR00400.PAYTYPE = @PType or @PType = 0)
and (UPR00400.PAYRCORD = @PCode or @PCode = 0)
...
ORDER BY
UPR00100.EMPLOYID ASC

==========================================
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-09 : 15:20:56

That would not work at all. Because the reason why I had this in there is because if a parameter is passed or not.

The end user will select a certain @PType or if they leave it as 0 then they want every @PType.

Our @PType = 1,2,5,9

So if they select 0 or blank then I want every @PType.

That is why I had this statement in there.

IF @PType <> '0' begin
SET @ENDSTATEMENT = " AND UPR00400.PAYTYPE = @PType"
END

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-09 : 15:29:27

your statement makes no sense:

First, each assignment of @ENDSTATEMENT completely overwrites the previous assignments; do you mean SET @ENDSTATEMENT = @ENDSTATEMNT + 'whatever' instead?

Next, you compare @Pcode to '0'; and then if that comparision holds, you say PAYRCORD must be equal to @PCode, which is '0' .... what does that do for you? what if @PCode is null? what if it is not equal to '0'?

What are you trying to do? Write it out in plain english what you are looking for.

- Jeff

vbjohn
Starting Member

32 Posts

Posted - 2003-04-09 : 15:33:32

Ok sorry.

I am writing code in visual basic and I should have stated...

SET @ENDSTATEMENT = @ENDSTATEMENT & " AND UPR00400.PAYTYPE = @PType"

When an end user does not select a certain Pay Type '@PType'. Then they should get all of the Pay Types. The Pay Types are coded... 1,2,5,9. I hope this clears it up.

John-

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources