stored procedure

  • Hello,

    I realise a buseiness intelligence project for an enterprise.

    I would like to know your advices because I don't know how to do:(

    I have a table "tb_beforeFact" and I have 2millions records in it.

    The table(fact table) is:

    IDCLI M2HBITA M3ETUD M2CONSO

    219 10 5 4

    518 0 2 3

    554 5 0 0

    904 0 2 6

    and the other table is (dimension table) "dim_credit":

    ID_CREDIT NAME

    1 habit

    2 etudiant

    3 conso

    I want to construct my proper table as using these 2 tables:

    The table I want to construct is:

    IDCLI ID_CREDIT AMOUNT

    219 1 10

    219 2 5

    219 3 4

    518 2 2

    518 3 3

    904 2 2

    904 3 6

    The problem that in the "tb_beforeFact" fact table they are too many records and I use SQL server 2008. i want to code a performant stored procedure and I think we can not use for or if statements in SQL?

    Cursor is it a good idea?

  • Hi.

    I'm not sure if I'm really answering your problem, but if you need a way to achieve the desired result, it's very simple to do so without any cursor. As for performance, I do not know how a solution like this one will react on 2 millions of rows.

    -- CREATE the tables we need

    DECLARE @tb_beforeFact TABLE

    (

    IDCLI int PRIMARY KEY,

    M2HBITA int,

    M3ETUD int,

    M2CONSO int

    )

    DECLARE @dim_credit TABLE

    (

    ID_CREDIT int PRIMARY KEY,

    NAME varchar(20) COLLATE Latin1_General_CI_AI

    )

    -- INSERT the test data we need

    INSERT @tb_beforeFact

    (

    IDCLI,

    M2HBITA,

    M3ETUD,

    M2CONSO

    )

    SELECT 219, 10, 5, 4 UNION ALL

    SELECT 518, 0, 2, 3 UNION ALL

    SELECT 554, 5, 0, 0 UNION ALL

    SELECT 904, 0, 2, 6

    INSERT @dim_credit

    (

    ID_CREDIT,

    NAME

    )

    SELECT 1, 'habit' UNION ALL

    SELECT 2, 'etudiant' UNION ALL

    SELECT 3, 'conso'

    -- CHECK the data as it is

    SELECT * FROM @tb_beforeFact

    SELECT * FROM @dim_credit

    -- SELECT the data as we want it

    SELECT

    tb_beforeFact.IDCLI,

    dim_credit.ID_CREDIT,

    CASE dim_credit.ID_CREDIT

    WHEN 1 THEN tb_beforeFact.M2HBITA

    WHEN 2 THEN tb_beforeFact.M3ETUD

    WHEN 3 THEN tb_beforeFact.M2CONSO

    END AS Amount

    FROM

    @tb_beforeFact AS tb_beforeFact

    INNER JOIN @dim_credit AS dim_credit

    ON CASE dim_credit.ID_CREDIT

    WHEN 1 THEN tb_beforeFact.M2HBITA

    WHEN 2 THEN tb_beforeFact.M3ETUD

    WHEN 3 THEN tb_beforeFact.M2CONSO

    END 0

    ORDER BY

    tb_beforeFact.IDCLI,

    dim_credit.ID_CREDIT

  • Hi,

    thank u so much for your kind answer, I didn't really waite as a good query ready to execute:) it is really easier than to use the cursor.

    But in fact it was only a part of my needs but this code gives me a good idea to improve my stored procedure

  • Glad I could help 🙂

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply