About naming parameters:

I have been using @pi_ prefix for "parameter, integer" and @ls_ prefix for "local variable, string".

I am one who uses very long stored proedures to say, process incoming Electronic Data Interchange incoming customer purchase orders with multiple validaton steps, etc. And I am not talking about a few hundred lines either. TGhink about an order of magnitude higher. All set-based but (quick, hide the children) procedural code.

Never swa the need to distihguish between input and output parameters, in fact, I have yet to use an output parameter. I welcome any feedback as how an output parameter could be useful in a practical applicaiton.

One thing I will do is return a result code to identify processing errors, and for this I use a local variable instead of an OUTPUT parameter.:

[font="Courier New"]DECLARE @li_RetCode int

SET @li_RetCode = -1

IF @@ERROR <> 0 SET @li_RetCode = -2 ELSE SET @l;i_RetCode = 0

IF @li_RetCode <> 0 RETURN @li_RetCode

IF @@ERROR <> 0 SET @li_RetCode = -2 [/font]

OR

[font="Courier New"]

IF @@ERROR <> 0 SET @li_RetCode = -2 ELSE SET @li_RetCode = 0

IF @li_RetCode = 0 BEGIN

.

. IF @@ERROR <> 0 SET @li_RetCode = -2

END[/font]

OR

DECLARE @lb_InTransaction bit

SET @lb_InTransaction = 0

IF @@ERROR <> 0 SET @li_RetCode = -2 ELSE SET @li_RetCode = 0

IF @li_RetCode <> 0 GOTO RATS

BEGIN TRAN

SET @lb_InTransaction = 0

IF @@ERROR <> 0 SET @li_RetCode = -2

IF @li_RetCode <> 0 GOTO RATS

IF @@ERROR <> 0 SET @li_RetCode = -2

IF @li_RetCode <> 0 GOTO RATS

COMMIT TRAN

RETURN @li_RetCode

RATS:

IF @lb_InTransaction = 1 ROLLBACK TRAN

RETURN @li_RetCode

Any constructive comment on the above is welcome, especially if someone can point out why this is a bad idea.