p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Language (http://p2p.wrox.com/forumdisplay.php?f=100)
-   -   how to improve this code? (http://p2p.wrox.com/showthread.php?t=38713)

dhaval229 February 20th, 2006 04:38 AM

how to improve this code?
 
hello friends

how to improve this code given below:


ALTER procedure Sp_NSE_DataPrepForRemissorCalc
@client_code varchar(10)
as

DECLARE
@remissor_code varchar(10),
@party_Code varchar(10), --displays code of party
@trade_no varchar(17),
@scrip_cd varchar(10),--displays scrip
@tradeqty int,-- displays quantity of trade
@series varchar(3),
@marketrate money,
@sauda_date datetime,--displays date and time of transaction
@trade_type char(1),
@leg_type CHAR(1),
@sell_buy INT,--says whether it is buy or sell
@sett_sales money,
@normal money,
@brk_100rs money,
@nbrokapp MONEY,-- displays brokerage applied
@min_shr money,
@sett_no VARCHAR(7),
@nsertax money,
@ins_chrg money,
@broker_chrg money,
@turn_tax money,
@billflag int,
@sub_broker varchar(10),
@table_no varchar(5),
@scheme_type varchar(5),
@to_date datetime,
@val_perc char(1),
@testbs INT,
@testqty int

begin

    --creation of table
    drop table trd_table
    create table trd_table (remissor_code varchar(10) NOT NULL,party_code VARCHAR(10) NOT NULL, sauda_date datetime NOT NULL, sett_no VARCHAR(7) NOT NULL, trade_no varchar(17) NOT NULL, scrip_cd varchar(10) NOT NULL, series CHAR(3) NOT NULL,
                tradeqty int NOT NULL, trade_type char(1) NOT NULL, leg_type char(1) NOT NULL, sell_buy INT NOT NULL, marketrate money NOT NULL, nsertax money NOT NULL, ins_chrg money NOT NULL, broker_chrg money NOT NULL, turn_tax money NOT NULL,
                 brk_100rs money NOT NULL, brk_shr MONEY NOT NULL, min_shr money NOT NULL)

    ---- declaring 1st cursor
    DECLARE distinct_partycode CURSOR FOR
        select distinct client.sub_broker,trade.party_Code
    from nseclient1 client,nsetrades trade
    where trade.contractno <> 0 and trade.sauda_date between 'Dec 1 2005' and 'Dec 31 2005'
              and trade.party_code=client.cl_code and left(ltrim(client.sub_broker),1) in (7,8)
              and len(ltrim(rtrim(sub_broker))) = 7

    --- opening 1st cursor
    OPEN distinct_partycode

    --- fetching values from 1st cursor for 1st time
    FETCH NEXT FROM distinct_partycode
    INTO @sub_broker,@party_code

    -- looping thru first cursor
    WHILE @@FETCH_STATUS = 0
    BEGIN

        ------- declaring 2nd cursor
        declare distinct_brokerage cursor for
        select distinct trade.party_code, trade.sett_sales,scheme.table_no,scheme.to_date,br oktable.[normal]
        from nsetrades trade,nseclientbrok_scheme scheme,nsebroktable broktable
        where scheme.table_no = broktable.table_no and scheme.party_code = trade.party_code and scheme.trade_type = 'NRM'
              and scheme.to_date like 'dec 31 2049%' and broktable.val_perc = 'P' and scheme_type = 'DEL'

        ------- opening 2nd cursor
        open distinct_brokerage

        ------- fetching value form 2nd cursor for 1st time
        fetch next from distinct_brokerage
        into @party_code,@sett_sales,@table_no,@to_date,@normal

        ------- looping through second cursor
        while @@fetch_status = 0
        begin

            ------- declaring 3rd cursor
            declare distinct_trades cursor for
            select trade_no,scrip_cd,marketrate,sell_buy,Tradeqty,nbr okapp,sauda_date,sett_no,nsertax,ins_chrg,broker_c hrg,turn_tax,billflag
            from nsetrades
            where party_code = @client_code and scrip_cd = @scrip_cd and series = @series and sett_no = @sett_no
            order by sauda_date --convert(varchar,sauda_date,109)

            --- opening of 3rd cursor
            open distinct_trades

            --- fetching values from 3rd cursor for the 1st time
            fetch next from distinct_Trades
            into @trade_no,@scrip_cd,@marketrate,@sell_buy,@Tradeqt y,@nbrokapp,@sauda_date,@sett_no,@nsertax,@ins_chr g,@broker_chrg,@turn_tax,@billflag
            set @testbs = @sell_buy
            set @testqty = 0

                --set @trade_type = 'TRD'

            /* If @billflag in (2,3)
            begin
            set @trade_type = 'TRD'
            end


            If @billflag in (4,5)
            begin
            -- set @leg_type = ' '
            set @trade_type = 'DEL'
            -- set @leg_type = ' '
            end

            */
            -- set @brk_100rs = @sett_sales
                set @min_shr = 0.0000

            ---looping thru 2rd cursor
            while @@fetch_status = 0
            begin
            If @billflag in (2,3)
            begin
                set @trade_type = 'T'
                set @brk_100rs = @sett_sales

                if @sell_buy <> @testbs
                begin
                    if @testqty >= @tradeqty
                    begin
                        set @testqty = @testqty - @tradeqty

                                               insert into trd_table (remissor_code,party_code, sauda_date, sett_no, trade_no, scrip_cd, series, tradeqty, trade_type, leg_type, sell_buy, marketrate, nsertax, ins_chrg, broker_chrg, turn_tax,
                                     brk_100rs, brk_shr, min_shr)
                                          values(@sub_broker,@party_code,@sauda_date,@sett_n o,@trade_no,@scrip_cd,@series,@tradeqty,@trade_typ e,'S',@sell_buy,@marketrate,@nsertax,@ins_chrg,@br oker_chrg,@turn_tax,@brk_100rs,@nbrokapp,@min_shr)

                    end
                    else
                    begin
                                            insert into trd_table (remissor_code,party_code, sauda_date, sett_no, trade_no, scrip_cd, series, tradeqty, trade_type, leg_type, sell_buy, marketrate, nsertax, ins_chrg, broker_chrg, turn_tax,
                                     brk_100rs, brk_shr, min_shr)
                                          values(@sub_broker,@party_code,@sauda_date,@sett_n o,@trade_no,@scrip_cd,@series,@testqty,@trade_type ,'S',@sell_buy,@marketrate,@nsertax,@ins_chrg,@bro ker_chrg,@turn_tax,@brk_100rs,@nbrokapp,@min_shr)
                        set @testqty = @tradeqty - @testqty
                        set @testbs = @sell_buy
                                            insert into trd_table (remissor_code,party_code, sauda_date, sett_no, trade_no, scrip_cd, series, tradeqty, trade_type, leg_type, sell_buy, marketrate, nsertax, ins_chrg, broker_chrg, turn_tax,
                                     brk_100rs, brk_shr, min_shr)
                                          values(@sub_broker,@party_code,@sauda_date,@sett_n o,@trade_no,@scrip_cd,@series,@testqty,@trade_type ,'F',@sell_buy,@marketrate,@nsertax,@ins_chrg,@bro ker_chrg,@turn_tax,@brk_100rs,@nbrokapp,@min_shr)

                    end
                end
                else
                begin
                    set @testqty = @testqty + @tradeqty
                                           insert into trd_table (remissor_code,party_code, sauda_date, sett_no, trade_no, scrip_cd, series, tradeqty, trade_type, leg_type, sell_buy, marketrate, nsertax, ins_chrg, broker_chrg, turn_tax,
                                 brk_100rs, brk_shr, min_shr)
                                  values(@sub_broker,@party_code,@sauda_date,@sett_n o,@trade_no,@scrip_cd,@series,@tradeqty,@trade_typ e,'F',@sell_buy,@marketrate,@nsertax,@ins_chrg,@br oker_chrg,@turn_tax,@brk_100rs,@nbrokapp,@min_shr)
                end
            end

        If @billflag in (4,5)
        begin
            set @leg_type = 'D'
            set @trade_type = 'D'
                   set @brk_100rs = @normal
        -- set @leg_type = ' '



            if @sell_buy <> @testbs
            begin
                if @testqty >= @tradeqty
                begin
                    set @testqty = @testqty - @tradeqty

                    insert into trd_table (remissor_code,party_code, sauda_date, sett_no, trade_no, scrip_cd, series, tradeqty, trade_type, leg_type, sell_buy, marketrate, nsertax, ins_chrg, broker_chrg, turn_tax,
                                     brk_100rs, brk_shr, min_shr)
                                  values(@sub_broker,@party_code,@sauda_date,@sett_n o,@trade_no,@scrip_cd,@series,@tradeqty,@trade_typ e,@leg_type,@sell_buy,@marketrate,@nsertax,@ins_ch rg,@broker_chrg,@turn_tax,@brk_100rs,@nbrokapp,@mi n_shr)
                end
                else
                begin
                                        insert into trd_table (remissor_code,party_code, sauda_date, sett_no, trade_no, scrip_cd, series, tradeqty, trade_type, leg_type, sell_buy, marketrate, nsertax, ins_chrg, broker_chrg, turn_tax,
                                     brk_100rs, brk_shr, min_shr)
                                  values(@sub_broker,@party_code,@sauda_date,@sett_n o,@trade_no,@scrip_cd,@series,@testqty,@trade_type ,@leg_type,@sell_buy,@marketrate,@nsertax,@ins_chr g,@broker_chrg,@turn_tax,@brk_100rs,@nbrokapp,@min _shr)

                    set @testqty = @tradeqty - @testqty
                    set @testbs = @sell_buy
                                        insert into trd_table (remissor_code,party_code, sauda_date, sett_no, trade_no, scrip_cd, series, tradeqty, trade_type, leg_type, sell_buy, marketrate, nsertax, ins_chrg, broker_chrg, turn_tax,
                                     brk_100rs, brk_shr, min_shr)
                                  values(@sub_broker,@party_code,@sauda_date,@sett_n o,@trade_no,@scrip_cd,@series,@testqty,@trade_type ,@leg_type,@sell_buy,@marketrate,@nsertax,@ins_chr g,@broker_chrg,@turn_tax,@brk_100rs,@nbrokapp,@min _shr)
                end
            end
            else
            begin
                set @testqty = @testqty + @tradeqty
                                insert into trd_table (remissor_code,party_code, sauda_date, sett_no, trade_no, scrip_cd, series, tradeqty, trade_type, leg_type, sell_buy, marketrate, nsertax, ins_chrg, broker_chrg, turn_tax,
                             brk_100rs, brk_shr, min_shr)
                                   values(@sub_broker,@party_code,@sauda_date,@sett_n o,@trade_no,@scrip_cd,@series,@tradeqty,@trade_typ e,@leg_type,@sell_buy,@marketrate,@nsertax,@ins_ch rg,@broker_chrg,@turn_tax,@brk_100rs,@nbrokapp,@mi n_shr)
            end
        end




            ---fetching from 3rd cursor
        fetch next from distinct_Trades
        into @trade_no,@scrip_cd,@marketrate,@sell_buy,@Tradeqt y,@nbrokapp,@sauda_date,@sett_no,@nsertax,@ins_chr g,@broker_chrg,@turn_tax,@billflag
    end

    CLOSE distinct_trades
    DEALLOCATE distinct_trades

            ---fetching from 2nd cursor
            fetch next from distinct_brokerage
            into @party_code,@sett_sales,@table_no,@to_date,@normal
    end

    CLOSE distinct_brokerage
    DEALLOCATE distinct_brokerage



        --fetching from 1st cursor
    FETCH NEXT FROM distinct_partycode
    INTO @sub_broker,@party_code
END

select * from trd_table
CLOSE distinct_partycode
DEALLOCATE distinct_partycode

end





dhaval joshi


All times are GMT -4. The time now is 10:49 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.