When try to save this procedure I am getting the error
MUST DECLARE A SCALAR VARIABLE "@code_value"
ALTER PROCEDURE p_trans
(
@date datetime,
@ref varchar(30)= null,
@dr_amt money=0,
@cr_amt money =0,
@posted_to integer,
@id char(1),
@doc_no integer = null,
@narr varchar(150) = null
)
AS
DECLARE @ll_doc integer
DECLARE @ret integer
DECLARE @code_value integer
SELECT @code_value = Selection
FROM tbl_selection
BEGIN TRANSACTION
IF isnull(@doc_no,0)=0
BEGIN
SELECT @ret=count(*)
FROM tr_header
WHERE ref=@ref
IF @ret>0
BEGIN
GOTO doerror
END
SELECT @ll_doc=isnull(max(doc_no),0)+1
FROM tr_header
IF @@ERROR != 0
BEGIN
GOTO doerror
END
END
ELSE
BEGIN
SET @ll_doc=@doc_no
DELETE FROM transactions
WHERE doc_no=@doc_no
IF @@ERROR != 0
BEGIN
GOTO doerror
END
END
BEGIN
INSERT INTO tr_header (id, date, ref, doc_no, narr)
VALUES (@id,isnull(@date,getdate()),@ref,@ll_doc,@narr)
IF @@ERROR != 0
BEGIN
GOTO doerror
END
INSERT INTO transactions (doc_no, dr_amount, cr_amount, code_value, sr_no, posted_to)
VALUES (@ll_doc,isnull(@dr_amt,0),isnull(@cr_amt,0),@code_vaue,1,@posted_to)
IF @@ERROR!=0
BEGIN
GOTO doerror
END
INSERT INTO transactions (doc_no, dr_amount, cr_amount, code_value, sr_no, posted_to)
VALUES (@ll_doc,isnull(@cr_amt,0),isnull(@dr_amt,0),@posted_to,2,@code_value)
IF @@ERROR!=0
BEGIN
GOTO doerror
END
END
COMMIT TRANSACTION
Select 0
GOTO doreturn
doerror:
ROLLBACK TRANSACTION
doreturn:
Return 0
Select -100
[edit]SHOUTING removed, Code block added - OriginalGriff[/edit]