Easy and Repeatable Testing of DB Code (Stored Procs, etc.) with DbFit

  • gojko adzic

    SSC Rookie

    Points: 41

    Solomon, in my experience the "SELECT * FROM Table1 to test that change" approach is very inefficient. Come to think of it, next month or next year you will have to add some more test data for some other requirement, and that will break your "test that change" piece. It is much better to test exactly the change - the difference between the original and the modified states.

    for large tables, if the table has an autoincremented id, you can do something like:

    |query|select max(id) as top from table|

    |top|

    |>>currentmax|

    // do your test here

    |query|select * from table where id>@currentmax|

    |col1|col2|col3|

  • Alexander Kuznetsov

    SSCrazy

    Points: 2217

    gojko adzic (11/21/2008)


    Solomon, in my experience the "SELECT * FROM Table1 to test that change" approach is very inefficient. Come to think of it, next month or next year you will have to add some more test data for some other requirement, and that will break your "test that change" piece. It is much better to test exactly the change - the difference between the original and the modified states.

    for large tables, if the table has an autoincremented id, you can do something like:

    |query|select max(id) as top from table|

    |top|

    |>>currentmax|

    // do your test here

    |query|select * from table where id>@currentmax|

    |col1|col2|col3|

    works only for inserts and only if the table has identity.

  • Ian Gregson

    SSC Rookie

    Points: 32

    I would like to be able to create a test around the metadata returned from a stored procedure.

    My scenario :- I will receive Column A, Column B & Column C from a stored procedure.

    If another developer changes the sproc to return another column, Column D, I want my test to fail.

    'Inspect Query' does return the metadata but I can't figure out a way of sticking that into a test.

    Any idea how I can acheive this?

  • Solomon Rutzky

    SSCoach

    Points: 15939

    ian.gregson (2/10/2011)


    I would like to be able to create a test around the metadata returned from a stored procedure.

    My scenario :- I will receive Column A, Column B & Column C from a stored procedure.

    If another developer changes the sproc to return another column, Column D, I want my test to fail.

    Hi Ian. The only way I can get this to work is to cause a SQL Server exception when the test runs as opposed to a handled DbFit error. I hope this ok as it does kinda work.

    1) Use the |Execute| method to create a temp table to hold the expected columns

    2) Use a second |Execute| to INSERT into the temp table from an EXEC to the Stored Proc

    Example:

    !|Execute| CREATE TABLE #ResultSetTest (first VARCHAR(50), second VARCHAR(50), third INT) |

    !|Execute| INSERT INTO #ResultSetTest (first, second, third) EXEC Test.dbo.TestProc |

    If the result set of the Proc does not match the temp table, then you will get an exception stating:

    System.Data.SqlClient.SqlException: Column name or number of supplied values does not match table definition.

    Take care,

    Solomon...

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sites — Collations     •     Module Signing     •     SQLCLR

  • Jeff Moden

    SSC Guru

    Points: 993884

    eharper (11/21/2008)


    Is there any way to increase the command timeout in DBFit?

    I would like to test some complex data warehouse ETL stored procedures, but I've hit a problem as several run for longer than the default timeout (30 seconds), even on test data.

    I'm not trying to be a "smart guy" here... I'm concerned about the code being tested. Wouldn't the fact that something takes longer than 30 seconds "even on test data" be the first indication of a possible major performance problem which needs to fixed prior to going to production?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Ian Gregson

    SSC Rookie

    Points: 32

    Hi Solomon, thanks for the reply but I didn't really want to execute the sproc in its entirety. Some of our report sprocs are complex and can take up to 40 seconds to complete.

    I was using the DBfit 'Inspect Query' command to execute the sproc with NULL variables as this instantly returns the 'result set' structure (the output column names) and not the actual 'result set' data.

    These output column names are what I want to build my test around but I can't figure out a way of grabbing them within a test.

  • Solomon Rutzky

    SSCoach

    Points: 15939

    ian.gregson (2/14/2011)


    Hi Solomon, thanks for the reply but I didn't really want to execute the sproc in its entirety.

    These output column names are what I want to build my test around but I can't figure out a way of grabbing them within a test.

    Hi Ian. There is no way that I can think of to just grab the result set column names.

    However, if you want to run the Proc with all parameters set to NULL, then just run it in an Execute while setting the variables to NULL, such as:

    !|Execute| CREATE TABLE #ResultSetTest (ColA INT, ColB VARCHAR(50), ColC DATETIME) |

    !|Execute| INSERT INTO #ResultSetTest EXEC Test.dbo.TestProc NULL, NULL, NULL |

    Or you can try setting the session property FMTONLY:

    !|Execute| CREATE TABLE #ResultSetTest (ColA INT, ColB VARCHAR(50), ColC DATETIME) |

    !|Execute| SET FMTONLY ON |

    !|Execute| INSERT INTO #ResultSetTest EXEC Test.dbo.TestProc NULL, NULL, NULL |

    !|Execute| SET FMTONLY OFF |

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sites — Collations     •     Module Signing     •     SQLCLR

Viewing 7 posts - 16 through 22 (of 22 total)

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