Contoh Stored Procedures SQL Server

Berikut Ini Contoh Stored Procedures Sederhana .

USE [ACCOUNTINGDB]

GO

/****** Object:  StoredProcedure [dbo].[spvw_SummFreight_ShippingCharge]    Script Date: 05/28/2014 08:18:34 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

-- Create store procedure

-- By Kusdi Mei-2014

CREATE PROCEDURE [dbo].[spvw_SummFreight_ShippingCharge]

(

@month int,

@year int

)

 AS

--deklarasi variable 

declare 

@FRD_FRDCode int,

@FI_CurrencyCode int, 

@air_import numeric(18,2),

@air_export numeric(18,2), 

@sea_import numeric(18,2), 

@sea_export numeric(18,2)

--set @month=5

--set @year=2014

begin 

    create table #Temp_FreightInvoice( 

no_urut int,

forwadercode int,

CurrencyCode int,

air_import numeric(18,2) null,

air_export numeric(18,2) null,

sea_import numeric(18,2) null,

sea_export numeric(18,2) null)

--select * from #Temp_FreightInvoice

--insert from  select

insert into #Temp_FreightInvoice

(forwadercode,CurrencyCode)

select FRD_FRDCode,FI_CurrencyCode   

from  accountingdb..TBTI_FreightInvoice 

inner join  accountingdb..vw_mst_currency on FI_CurrencyCode  = cast(CRC_CurrencyCode as int)

inner join PPSDB..TB_MST_Forwarders on FRD_FRDCode = FI_ForwarderCode

where FI_flag <> 25 AND FI_Source = 22 and FI_Flag = 2 and month(FI_PaymentDate)=@month and  year(FI_PaymentDate)=@year

group by FRD_FRDCode,FI_CurrencyCode

    --buat deklarasi cursor

declare FreightInvoice cursor fast_forward for

select FRD_FRDCode,FI_CurrencyCode      

from  accountingdb..TBTI_FreightInvoice 

inner join  accountingdb..vw_mst_currency on FI_CurrencyCode  = cast(CRC_CurrencyCode as int)

inner join PPSDB..TB_MST_Forwarders on FRD_FRDCode = FI_ForwarderCode

where FI_flag <> 25 AND FI_Source = 22 and FI_Flag = 2 and month(FI_PaymentDate)=@month and  year(FI_PaymentDate)=@year

group by FRD_FRDCode,FI_CurrencyCode

Open FreightInvoice

Fetch next from FreightInvoice into @FRD_FRDCode,@FI_CurrencyCode

while @@Fetch_status = 0

begin

--summ air - import

select  @air_import= sum(FI_Amount)  

from  accountingdb..TBTI_FreightInvoice

where month(FI_PaymentDate)=@month and  year(FI_PaymentDate)=@year   

and FI_ShippingMode='Air - Import' and FI_ForwarderCode=@FRD_FRDCode 

and FI_CurrencyCode=@FI_CurrencyCode

  

--summ Air - Export

select @air_export= sum(FI_Amount)

from  TBTI_FreightInvoice

where month(FI_PaymentDate)=@month and  year(FI_PaymentDate)=@year 

and FI_ShippingMode='Air - Export' and FI_ForwarderCode=@FRD_FRDCode

and FI_CurrencyCode=@FI_CurrencyCode

--summ Sea - Import

select  @sea_import= sum(FI_Amount)

from  TBTI_FreightInvoice

where month(FI_PaymentDate)=@month and  year(FI_PaymentDate)=@year 

and FI_ShippingMode='Sea - Import' and FI_ForwarderCode=@FRD_FRDCode

and FI_CurrencyCode=@FI_CurrencyCode

--summ Sea - Import

select @sea_export= sum(FI_Amount)

from  TBTI_FreightInvoice

where month(FI_PaymentDate)=@month and  year(FI_PaymentDate)=@year 

and FI_ShippingMode='Sea - Export' and FI_ForwarderCode=@FRD_FRDCode

and FI_CurrencyCode=@FI_CurrencyCode   

   

   --update temp tabel  

update #Temp_FreightInvoice

set 

air_import=@air_import,

air_export=@air_export,

sea_import=@sea_import,

sea_export=@sea_export

Where forwadercode =@FRD_FRDCode and CurrencyCode =@FI_CurrencyCode

Fetch next from FreightInvoice into @FRD_FRDCode,@FI_CurrencyCode

end

Close FreightInvoice

Deallocate FreightInvoice 

end   

--select data temporary

select FRD_FRDName, CRC_CurrencyName, air_import, air_export ,sea_import , sea_export

from #Temp_FreightInvoice

inner join accountingdb..vw_mst_currency on CurrencyCode   = cast(CRC_CurrencyCode as int)

inner join PPSDB..TB_MST_Forwarders on  forwadercode=FRD_FRDCode 

--drop temporary table 

Drop table #Temp_FreightInvoice