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

Question

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

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

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

gojko adzic SSC Rookie Points: 41 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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: 16430 More actions · Answer 4

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 Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 5

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ian Gregson SSC Rookie Points: 32 More actions · Answer 6

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: 16430 More actions · Answer 7

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 Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR