Skip to main content

Posts

Showing posts with the label example select query

Difference between alias and synonym in sql

Difference between  alias and synonym in sql Both are different in all manner not only syntactically Synonym is 1. It is a database object 2. It may be Public or private 3. It is permanent until dropped 4. create [public] synonyms syn_name for [Existed database object]; like create synonym emp1  for hr.employees; Alias is 1. It is not a database object. 2. It will alway private to query 3. It temporary to that query.It to used to provide a name It is generally used to provide a meaning-full heading or column name or table name that is long or inconvenient to type in Query 4. take a example select rownum as rank,e.* from emp1 e; here rank and e is alias where as emp1 is synonym

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