I try to through this code
- try
- {
- SqlCommand cmd = new SqlCommand("dbo.generate_loan_entry", connect);
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.Parameters.AddWithValue("@farmer_code", party_code.Text);
- cmd.Parameters.AddWithValue("@ticket_no", ticket_no.Text);
- cmd.Parameters.AddWithValue("@total_loan", adj_loan);
- connect.Open();
- cmd.ExecuteNonQuery();
- connect.Close();
- }
- catch (Exception ex)
- {
- Response.Write("<script>alert('" + ex.Message + "')</script>");
- }
- finally
- { }
My procedure is
- ALTER PROCEDURE [dbo].[generate_loan_entry](@farmer_cd nvarchar(6),
- @ticket_no int,
- @total_loan int)
-
- AS
- BEGIN
-
-
- SET NOCOUNT ON;
-
- DECLARE @v_account_code INT,
- @vloan_amount numeric(8,2),
- @vadj_loan numeric(8,2),
- @vloan_deduct numeric(8,2),
- @vdeducted_loan numeric(8,2);
-
- DECLARE @Counter INT
- SET @Counter = 1
-
- DECLARE C1 CURSOR READ_ONLY
- FOR
- SELECT account_code,sum(DEBIT_AMOUNT-CREDIT_AMOUNT)
- FROM FA_BALANCE_MASTER
- WHERE ACCOUNT_CODE IN (211068,211069) AND PARTY_CODE = @farmer_cd
- AND YYYYMM >= (Select year(start_date) from fa_company_detail )
- group by account_code;
-
- OPEN C1;
-
- FETCH NEXT FROM C1 INTO @v_account_code,@vloan_amount;
-
- WHILE @@FETCH_STATUS = 0
- Begin
-
- SELECT @vloan_deduct = SUM(adjusted_amount)
- FROM CA_LOAN_DEDUCTION
- WHERE ACCOUNT_CODE = @v_account_code
- AND PARTY_CODE = @farmer_cd AND isnull(voc_auth_flag,'N') = 'N';
- if (@vloan_deduct > 0.0 )
- begin
- select @vloan_amount = @vloan_amount - @vloan_deduct;
- end ;
- IF (@vloan_amount < isnull(@total_loan,0) - isnull(@vadj_loan,0))
- begin
- select @vdeducted_loan = @vloan_amount ;
- end;
- ELSE
- begin
- select @vdeducted_loan = isnull(@total_loan,0) - isnull(@vadj_loan,0);
- end;
- FETCH NEXT FROM C1 INTO @v_account_code,@vloan_amount;
- IF @vdeducted_loan > 0
- begin
- INSERT INTO CA_LOAN_DEDUCTION VALUES(
- @ticket_no,@v_account_code,@farmer_cd,@vloan_amount,@vdeducted_loan,null,'N');
- end;
- set @vadj_loan = @vadj_loan + @vdeducted_loan;
- end;
- close C1;
- DEALLOCATE C1;
- END;
please suggest what is the problem. this procedure running through execute procedure i check it