Skip to main content

Posts

Showing posts with the label grand total from sql sp

Stored Procedure example to get Grand Total using query

This is example of Stored Procedure to get Grand Total of columns using select query in sql. You can try for your database table too. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO Create PROCEDURE [dbo].[CALC_TOTAL1] ( @NO CHAR(15), @AMTNUMERIC1(9,2)=NULL, @TAX NUMERIC1(9,2)=NULL, @DISCNUMERIC1(9,2)=NULL, @G_TOTAL NUMERIC1(9,2)=NULL ) AS SELECT @TOT_AMOUNT=SUM(AMOUNT) FROM TRANS WHERE TNO=@CNO SELECT @TAX=ISNULL(SUM(TAX_A),0) FROM TAX WHERE NO=@TCNO SELECT @TOT_DISC_A=ISNULL(SUM(DISC_A),0) FROM DISCOUNT WHERE NO=@CNO SET @G_TOTAL = (@AMT- @TOT_DISC_A) + @TAX UPDATE FOOT SET SUBTOTAL=@TOT_AMOUNT,TAX=@TAX,DISCOUNT=@TOT_DISC_A,G_TOTAL=@G_TOTAL WHERE CNO=@CNO