Microsoft SQL Server 存储过程举例
时间：2021-07-01 10:21:17
帮助过：10人阅读
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
Microsoft SQL Server 存储过程举例
标签：
