General discussion

Locked

Mssql programming

By redcolony53 ·
Hi, i'm new in mssql programming...

i need a help.. please look my coding below.
if there is unnecessary line or bad coding..
PLEASE tell me

TQ.

CREATE PROCEDURE [test1] AS
Begin Tran
update jobdt set nstatusid=0 where nstatusid is null or nstatusid<0
IF @@ERROR <> 0 ROLLBACK TRAN

Declare myjobdun_cursor CURSOR FOR
SELECT nJobDTID, nStatusID FROM JobDT ORDER BY nJobDTID
IF @@ERROR <> 0 ROLLBACK TRAN

Declare @njobdtid int, @nstatusid int
OPEN myjobdun_cursor
FETCH NEXTFROM myjobdun_cursor
INTO @njobdtid, @nstatusid

IF @@ERROR <> 0 ROLLBACK TRAN
WHILE @@FETCH_STATUS =0
BEGIN
UPDATE JobDT SET nCumulatedQuantity= (SELECT SUM(nJobDunQuantity) FROM JobDunDT WHERE nJobDTID =@njobdtid) WHERE nJobDTID =@njobdtid
IF @@ERROR <> 0 ROLLBACK TRAN
if @nstatusid !=0
BEGIN
UPDATE JobDT SET nCumPrevious =(SELECT (ISNULL(nCumulatedQuantity,0) + ISNULL(nCumPrevious,0)) FROM JobDT WHERE nJobDTID =@njobdtid) WHERE nJobDTID =@nstatusid
IF @@ERROR <> 0 ROLLBACK TRAN
END

This conversation is currently closed to new comments.

9 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Mssql programming

by msullivan In reply to Mssql programming

One word...


COMMENTS!


Mike

Collapse -

Mssql programming

by redcolony53 In reply to Mssql programming

The question was auto-closed by TechRepublic

Collapse -

Mssql programming

by jtrifts In reply to Mssql programming

There are a few things your code could do with from a maintenance aspect.
As someone has alread said, comments in the code are essential. It might seem very straightforward to you right now, but how about if someone else looks at it in a year's time?

Next, for readability, I would suggest the use of some structured coding standards that consider the use of capitalisation for RESERVED_WORDS, good use of indentation: eg
SELECT column1, column2
FROM table1, table2
WHERE column1 = 'A'
AND column2 <> 'B';

Also, consider consistency. In one place you use '!=' and in another you use '<>'. While these are correct, they are inconsistent.

Similarly, in places you use 'if' and others 'IF'.

Indentation is important in improving readability of code. One of my leads once said that if you turn a page of code on its side it should look like a city skyline, not like a box.

Hope this helps,

Regards,
JT

Collapse -

Mssql programming

by redcolony53 In reply to Mssql programming

The question was auto-closed by TechRepublic

Collapse -

Mssql programming

by brenlee1 In reply to Mssql programming

A few things--
I concur that the code needs comments. At first glance, it looks okay, but since I don't know what you are attempting it is difficult to make an informed judgement. I usually coach programmers to write an "outline" of what they will do as their comments, then fill in the code. It will keep you focused on what you are doing and will be more understandable to others (and to yourself at a later date).

Also, it is a good practice to be as specific as possible when making declarations. I note that you didn't specify the type of cursor you wanted. You may want to try declaring it as a scroll cursor.

Collapse -

Mssql programming

by redcolony53 In reply to Mssql programming

The question was auto-closed by TechRepublic

Collapse -

Mssql programming

by DaveMcRae In reply to Mssql programming

jtrifts said it all.
But also I don't think the
IF @@ERROR <> 0 ROLLBACK TRAN
is what you're after - ie your SP will still try to execute the next statement. Try
..
IF (@@Error<>0) GOTO Error
....your other code
--Success
COMMIT
GOTO Xit
Error:
ROLLBACK TRAN
Xit:

Hope this makes sense.

Collapse -

Mssql programming

by redcolony53 In reply to Mssql programming

The question was auto-closed by TechRepublic

Collapse -

Mssql programming

by redcolony53 In reply to Mssql programming

This question was auto closed due to inactivity

Back to Web Development Forum
9 total posts (Page 1 of 1)  

Software Forums