March 26, 2011

get Grand Total using query through stroed procedure


 @AMTNUMERIC(9,2)=NULL,  
 @TAX NUMERIC(9,2)=NULL,  
 @DISCNUMERIC(9,2)=NULL,  
 @G_TOTAL NUMERIC(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