Microsoft SQL Server 存储过程举例

-- if SP is existed, drop it.
if (object_id(‘InvHoldToDPS‘, ‘P‘) is not null)    
    drop proc InvHoldToDPS
GO

-- craete new SP.
CREATE PROCEDURE dbo.InvHoldToDPS
AS
BEGIN
    SET NOCOUNT ON
    
    -- delete target table.
    --delete from inv1_holdsForPrice
    
    -- delte temp table.
    --delete from inv1_holdsForPriceTemp
    
    declare @headLine char(2000)
    -- get the first record which is title info.
    set @headLine = (select top 1 feedValue from inv1_holdsForPriceTemp)
    
    IF LEFT(@headLine, 6) <> ‘IMAHDR‘ 
    BEGIN
        print ‘Error‘
        -- wirte the info to the log table.
        -- exit (return)
    END
    
    declare @priceDate char(8)
    select @priceDate = SUBSTRING(@headLine, 26,8) -- store Price Date "YYYYMMDD"
    
    declare @j int
    select @j = 0
    
    declare curFV_cur cursor for
            select feedValue from inv1_holdsForPriceTemp for read only

        declare @feedValue char(2000)
        select @feedValue = ‘‘

        open curFV_cur
        fetch next from curFV_cur into @feedValue
        
        while (@@fetch_status = 0)
        begin
            if(LEFT(@feedValue, 6) <> ‘IMAHDR‘ And Left(@feedValue, 6) <> ‘IMATRL‘) --  Head and Feet record
            begin
                if(LEN(ltrim(rtrim(SUBSTRING(@feedValue, 39, 9))))) = 9
                begin
                    declare @inv1_acct_num     decimal
                    declare @inv1_grp_acct_num varchar
                    declare @fmr_fund_num      varchar
                    declare @fmr_subport_num   char
                    declare @fmr_cusip         varchar
                    declare @acct              varchar
                    declare @scty_cusip        varchar
                    declare @scty_date         varchar
                    declare @scty_qual         varchar
                    declare @hold_date         varchar
                    declare @shares            decimal
                    declare @cost               decimal    
                    declare @amort             decimal
                    declare @accr_incom        decimal
                    declare @market            decimal
                    declare @price             decimal
                    declare @fx_rate           decimal
                    declare @price_date        varchar
                                    
                    select @inv1_acct_num     = LTRIM(RTRIM(SUBSTRING(@feedValue,  1, 15)))
                    select @inv1_grp_acct_num = LTRIM(RTRIM(SUBSTRING(@feedValue, 16, 15)))
                    select @fmr_fund_num      = LTRIM(RTRIM(SUBSTRING(@feedValue, 31,  6)))
                    select @fmr_subport_num   = LTRIM(RTRIM(SUBSTRING(@feedValue, 37,  2)))
                    select @fmr_cusip         = LTRIM(RTRIM(SUBSTRING(@feedValue, 39,  9)))
                    select @acct              = LTRIM(RTRIM(SUBSTRING(@feedValue, 59, 15)))
                    select @scty_cusip        = LTRIM(RTRIM(SUBSTRING(@feedValue, 74,  9)))
                    select @scty_date         = LTRIM(RTRIM(SUBSTRING(@feedValue, 83,  8)))
                    select @scty_qual         = LTRIM(RTRIM(SUBSTRING(@feedValue, 91,  5)))
                    select @hold_date         = LTRIM(RTRIM(SUBSTRING(@feedValue, 96,  8)))
                    select @shares            = SUBSTRING(@feedValue, 104, 15) + ‘.‘ + SUBSTRING(@feedValue, 109, 4)
                    select @cost              = SUBSTRING(@feedValue, 123, 16) + ‘.‘ + SUBSTRING(@feedValue, 139, 2)
                    select @amort             = SUBSTRING(@feedValue, 141, 16) + ‘.‘ + SUBSTRING(@feedValue, 157, 2)
                    select @accr_incom        = SUBSTRING(@feedValue, 159, 16) + ‘.‘ + SUBSTRING(@feedValue, 175, 2)
                    select @market            = SUBSTRING(@feedValue, 177, 16) + ‘.‘ + SUBSTRING(@feedValue, 193, 2)
                    select @price             = SUBSTRING(@feedValue, 195, 77) + ‘.‘ + SUBSTRING(@feedValue, 206, 8)
                    select @fx_rate           = SUBSTRING(@feedValue, 214,  7) + ‘.‘ + SUBSTRING(@feedValue, 211, 9)
                    select @price_date        = @priceDate
                    
                    if(SUBSTRING(@feedValue, 48, 1)) = ‘1‘
                    begin
                        exec dbo.spUpd_inv1_holdsForPrice     
                                    @inv1_acct_num,
                                    @inv1_grp_acct_num,
                                    @fmr_fund_num,
                                    @fmr_subport_num,
                                    @fmr_cusip,
                                    @acct,
                                    @scty_cusip,
                                    @scty_date,
                                    @scty_qual,
                                    @hold_date,
                                    @shares,
                                    @cost,
                                    @amort,
                                    @accr_incom,
                                    @market,
                                    @price,
                                    @fx_rate,
                                    @price_date
                                                                                      
                    end
                    else if(SUBSTRING(@feedValue, 48, 1)) = ‘2‘
                    begin
                        declare @inv1_acct_num2     decimal
                        declare @inv1_grp_acct_num2 varchar
                        declare @fmr_fund_num2      varchar
                        declare @fmr_subport_num2   char
                        declare @fmr_cusip2         varchar    
                        declare @sec_desc           varchar
                        declare @sec_country_code   varchar
                        declare @traded_ccy         varchar
                        declare @income_ccy         varchar
                        declare @unit_of_calc       decimal
                            
                        select @inv1_acct_num2     = LTRIM(RTRIM(SUBSTRING(@feedValue,  1, 15)))
                        select @inv1_grp_acct_num2 = LTRIM(RTRIM(SUBSTRING(@feedValue, 16, 15)))
                        select @fmr_fund_num2      = LTRIM(RTRIM(SUBSTRING(@feedValue, 31,  6)))
                        select @fmr_subport_num2   = LTRIM(RTRIM(SUBSTRING(@feedValue, 37,  2)))
                        select @fmr_cusip2         = LTRIM(RTRIM(SUBSTRING(@feedValue, 39,  9)))
                        
                        select @sec_desc           = LTRIM(RTRIM(SUBSTRING(@feedValue, 59, 30)))
                        select @sec_country_code   = LTRIM(RTRIM(SUBSTRING(@feedValue, 89,  4)))
                        select @traded_ccy         = LTRIM(RTRIM(SUBSTRING(@feedValue, 93,  4)))
                        select @income_ccy         = LTRIM(RTRIM(SUBSTRING(@feedValue, 97,  4)))
                        select @unit_of_calc       = LTRIM(RTRIM(SUBSTRING(@feedValue, 158,  10))) + ‘.‘ + LTRIM(RTRIM(SUBSTRING(@feedValue, 168,  4)))
                                
                        exec spUpd_inv1_holdsForPrice2
                                    @inv1_acct_num2,
                                    @inv1_grp_acct_num2,
                                    @fmr_fund_num2,
                                    @fmr_subport_num2,
                                    @fmr_cusip2,
                                    @sec_desc,
                                    @sec_country_code,
                                    @traded_ccy,
                                    @income_ccy,
                                    @unit_of_calc
                    end
                end
            end
            
            select @j = @j + 1
            fetch next from curFV_cur into @feedValue
        end
        close curFV_cur
        deallocate curFV_cur    
    
END
SET NOCOUNT OFF

--exec spUpdateBDAatInv1
    
--exec spUpdateEquitiesMasterInv1
    
GO