Click here to Skip to main content
11,484,528 members (64,645 online)

Phani Bharadwaj asked:

Open original thread
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
	
	
	/* Get the selected cash/ bank account:
	The user makes a selection from selection.aspx and 
	tbl_section is update with the code_value
	*/
	SELECT @code_value = Selection 
	FROM tbl_selection
	
	BEGIN TRANSACTION
	
	IF isnull(@doc_no,0)=0
	--- INSERT---
	
	BEGIN
	--- Safe Guard: Check if transaction with same ref# exists. If so do not insert -----
	
		SELECT @ret=count(*) 
		FROM tr_header 
		WHERE ref=@ref
		
		IF @ret>0
		BEGIN
		--- raise error(5300,1,16)
			GOTO doerror
		END
		
		SELECT @ll_doc=isnull(max(doc_no),0)+1 
		FROM tr_header
		
		IF @@ERROR != 0
		BEGIN
			GOTO doerror
		END
	END
	
	ELSE
	---------- UPDATE----------
	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]
Tags: StoredProcedures

Preview



When answering a question please:
  1. Read the question carefully.
  2. Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
  3. If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Let's work to help developers, not make them feel stupid.
Please note that all posts will be submitted under the The Code Project Open License (CPOL).



Advertise | Privacy | Mobile
Web04 | 2.8.150520.1 | Last Updated 26 Mar 2009
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100