Testing the Stroed Procedure

Jul 3, 2009 at 11:04 AM

HI , 

After seeing the demo in youtube , am trying to use T.S.T for testing my stored procedure. I have 2 types SP's one is a aggregration kind of SP ,( collect data from 1 or more tables according to few mapping conditions and put it in another table. another kind of SP is calcuation (calcuate profit based on few table field values and generate another result table. 

now can you plz tell me that is it possible to automate these SP's using T.S.T ?

Thanks

Syam

 

Jul 3, 2009 at 3:59 PM

Syamchandran,

 

Are you asking “is it possible to test those stored procedures using T.S.T. ?”

or are you asking once you have written the test “is it possible to automate the execution of those tests?”

 

The answer is yes to both but I wanted to be sure what you are asking before giving a more detailed answer.

 

Jul 6, 2009 at 6:57 AM

Hi , here is my question with an example, 

This is an simple SP , which extracts data from 2-3 tables according to some filter conditions . 

 

======================================The SP follows==================================

ALTER PROCEDURE [dbo].[PopulateCityDimension]

AS

BEGIN

      SET NOCOUNT ON;

      BEGIN TRY

      BEGIN TRANSACTION PopulateCityDimension

 

      DECLARE @CIDfornull INT

      DECLARE @SIDfornull INT

      DECLARE @statCname VARCHAR(30)

 

      DECLARE @CountrykeyID INT

      DECLARE @StatekeyID INT

      DECLARE @StateCountStat VARCHAR(50)

     

      SELECT @StatekeyID=StatekeyId FROM StateDimension

      IF (@@rowcount=0)

      BEGIN

            SET @StateCountStat='Statevalueerror'

            SELECT 1/0

      END        

 

      SELECT @CountrykeyID=CountrykeyId FROM CountryDimension

      IF (@@rowcount=0)

      BEGIN

            SET @StateCountStat='Countryvalueerror'

            SELECT 1/0

      END

 

--    SELECT @StatekeyID=StatekeyId FROM StateDimension

--    IF (@@rowcount=0)

--    BEGIN

--          SET @StateCountStat='Statevalueerror'

--          SELECT @CountrykeyID=CountrykeyId FROM CountryDimension

--          IF (@@rowcount=0)

--          BEGIN

--                SET @StateCountStat='Countryvalueerror'

--          END

--          SELECT 1/0

--    END

 

      Select @SIDfornull=StateKeyID from dbo.StateDimension Where StateName='UNKNOWN' AND StateID='UNKNOWN'

      Select @CIDfornull=CountryKeyID from dbo.CountryDimension Where countryname='UNKNOWN' AND CountryID='UNKNOWN'

 

      UPDATE CityDimension

      Set CityName=CASE WHEN A.cname IS NULL THEN 'UNKNOWN' ELSE A.cname END,

            StateKeyID=CASE WHEN A.statekeyid IS NULL THEN @SIDfornull ELSE A.statekeyid END,

            CountryKeyID=CASE WHEN A.countrykeyid IS NULL THEN @CIDfornull ELSE  A.countrykeyid END

      From CityDimension CD

      LEFT OUTER JOIN

      (

      SELECT DISTINCT ISNULL(LTRIM(RTRIM(C.cName)),'') as cname,

               CASE WHEN SD.StateKeyID IS NULL THEN @SIDfornull ELSE SD.StateKeyID END as statekeyid,

               CASE WHEN CD.CountryKeyID IS null THEN @CIDfornull ELSE CD.CountryKeyID END as countrykeyid,

               C.cid as cityid

    FROM BIC_Inter_TestDataBase_QA.dbo.city C

      LEFT OUTER JOIN dbo.statedimension SD ON CONVERT(NVARCHAR,C.sid)=Convert(NVARCHAR,SD.StateID) --AND SD.StateID<>'UNKNOWN'

      LEFT OUTER JOIN dbo.countrydimension CD on CONVERT(NVARCHAR,C.ctid) =Convert(NVARCHAR,CD.CountryID)-- AND CD.countryID <>'UNKNOWN'

      )A

      on Convert(NVARCHAR,CD.CityID)=CONVERT(NVARCHAR,A.cityid)

      WHERE ((CD.CityID='UNKNOWN') OR (CD.CityID IN (SELECT CiD FROM BIC_Inter_TestDataBase_QA.dbo.City)))

           

 

     

      INSERT INTO dbo.CityDimension

                  (

                        CityName,

                        StateKeyID,

                        CountryKeyID,

                        CityID

                  )

      SELECT DISTINCT ISNULL(LTRIM(RTRIM(C.cName)),''),

               CASE WHEN SD.StateKeyID IS NULL THEN @SIDfornull ELSE SD.StateKeyID END,

               CASE WHEN CD.CountryKeyID IS null THEN @CIDfornull ELSE CD.CountryKeyID END,

               C.cid

    FROM BIC_Inter_TestDataBase_QA.dbo.city C

      LEFT OUTER JOIN dbo.statedimension SD ON C.sid=Convert(INT,SD.StateID) AND SD.StateID<>'UNKNOWN'

      LEFT OUTER JOIN dbo.countrydimension CD on C.ctid =Convert(INT,CD.CountryID) AND CD.countryID <>'UNKNOWN'

      Where C.cid NOT IN (SELECT Convert(INT,CityID) FROM CityDimension WHERE CityID<>'UNKNOWN')

 

      SELECT @statCname=CityName FROM CityDimension Where CityName='UNKNOWN' AND CityID='UNKNOWN'

      IF(@@ROWCOUNT=0)

      BEGIN

            --Select @CIDfornull=CountryKeyID from dbo.CountryDimension Where countryname='UNKNOWN'

            --Select @SIDfornull=StateKeyID from dbo.StateDimension Where StateName='UNKNOWN'

            INSERT INTO CityDimension(CityName,StateKeyID,CountryKeyID,CityID)

            Values('UNKNOWN',@SIDfornull,@CIDfornull,'UNKNOWN')

      END

     

      --DROP TABLE #tfornull

                   

            COMMIT TRANSACTION PopulateCityDimension

      END TRY    

      --Error Handle

      BEGIN CATCH

 

            DECLARE @ErrorMessage   VARCHAR(500),

                        @ErrorLine        INT,

                        @ErrorNumber      INT

     

            IF (@StateCountStat='Statevalueerror')

            BEGIN

                  SET @ErrorMessage='No values in StateDimension Table,Execute PopulateStateDimension SP and then TRY'

                  SET @ErrorNumber=-1000             

            END

            ELSE IF (@StateCountStat='Countryvalueerror')

            BEGIN

                  SET @ErrorMessage='No values in CountryDimension Table,Execute PopulateCountryDimension SP and then TRY'      

                  SET @ErrorNumber=-1000 

            END

            ELSE

            BEGIN

 

                  SELECT      @ErrorMessage = error_message(),@ErrorLine = error_line(),@ErrorNumber=@@ERROR

            END

 

            ROLLBACK TRANSACTION PopulateCityDimension

           

            DECLARE @RunSeqence     BIGINT

                  SET @RunSeqence = (SELECT MAX(RunSequence) FROM  TestDataBaseSSIS.dbo.PackageRunSequence

                                                WHERE PackageName = 'TestDataBase.dtsx')

 

            INSERT INTO TestDataBaseSSIS.dbo.UploadStatus

                  (

                        RunSequence,

                        ObjectName,

                        ObjectType,

                        StatusCode,

                        StausDescription,

                        ErrorAtLine,

                        RecordedAt,

                        Succeed

                  )

            VALUES(     @RunSeqence,

                        'PopulateCityDimension',

                        'StoredProcedure',

                        @ErrorNumber,

                        @ErrorMessage,

                        @ErrorLine,

                        GETDATE(),

                        0

                    )

           

            RAISERROR('Error Generated From Procedure',16,1)

      END CATCH  

   

END

=======================================================

The algoritham/request document has given below,

==================================

Firstly we need to update the existing records, update the field BIRetail_TestDataBase. CityDimension.Cityname with BIC_Inter_TestDataBase.dbo.City.CnamewhereBIRetail_TestDataBase. CityDimension.CityID =BIC_Inter_TestDataBase.dbo.City.Cid

The Mapping for finding the new records for State is replicated here, select only the city records from BIC_Inter_TestDataBase.dbo.city whereCid is does not exists inBIRetail_TestDataBase.CityDimension's CityID column

 

 

BIRetail_TestDataBase. Citydimension

Data From

Remarks

Citykeyid

-

Auto generated as it is an Identity Field

Cityname

BIC_Inter_TestDataBase.dbo.City. Cname

Statekeyid

BIRetail_TestDataBase.dbo.statedimension .Statekeyid

Relationship for getting the Statekeyid from Statedimension :

BIC_Inter_TestDataBase.dbo.City. Sid=BIRetail_TestDataBase.dbo.Statedimension .StateID

countrykeyid

BIRetail_TestDataBase.dbo.countrydimension.Countrykeyid

Relationship for getting the countrykeyid from countrydimension :

BIC_Inter_TestDataBase.dbo.City.Ctid=BIRetail_TestDataBase.dbo.countrydimension .countryID

CityID

BIC_Inter_TestDataBase.dbo.City. Cid

 

 

 

 

 

 

Step 1:

Check if a record is there with a Citynamein BIRetail_TestDataBase. CityDimension as "UNKNOWN"

if the record is not found insert a row with City name as "UNKNOWN" , CityID as "UNKNOWN" and countrykeyid with the value of countrykeyidfromBIRetail_TestDataBase. CountryDimension wherecountryname="Unknown" and value of statekeyidfrom BIRetail_TestDataBase. CityDimensionwhereCityname="Unknown"

Conditions the SP should satisfy:

-----------------------------------

i) Each time when the SP is executed it should only fetch the records from BIC_Inter_TestDataBase.dbo.city table where the particular cIDwhich is not existing incityIdcolumn of BIRetail_TestDataBase.dbo.citydimensiontable .

ii) IfBIC_Inter_TestDataBase.dbo.city.ctidis null insert the Country with the country name as "Unknown"

iii) IfBIC_Inter_TestDataBase.dbo.city.sidis null insert the state with the state name as "Unknown"

iv) If the BIC_Inter_TestDataBase.dbo.City.Cnameis Null insert blank

At all times the total records in BIRetail_TestDataBase.dbo.CityDimensionshould always be greater than the records in BIC_Inter_TestDataBase.dbo.City

================================================

Now how can i test this using T.S.T?

Plz explain me . 

 

 

 

Jul 7, 2009 at 9:46 PM

Here is what you should do:

  • Isolate all different requirements of the stored procedure.
  • Define for each of these requirements the scenarios that need testing.
  • Write a test procedure for each of these scenarios.

What follows is just an example and you may want to come up with a more comprehensive set of scenarios to test.
I see that you update and insert into CityDimension table. You update the data in CityDimension when you find matching records in BIC_Inter_TestDataBase_QA.dbo.city. You insert data when you find in table BIC_Inter_TestDataBase_QA.dbo.city entries that are new (do not match entries in CityDimension table).


Some of the possible scenarios for procedure PopulateCityDimension would be:

  1. The CityDimension table does not have an “Unknown” record.
  2. You have a record in BIC_Inter_TestDataBase_QA.dbo.city that matches by ID one in CityDimension table. However the city name is different between the two tables.
  3. You have a record in BIC_Inter_TestDataBase_QA.dbo.city that is new (does not match one in CityDimension table). All the relevant IDs (ctid, sid) are not NULL
  4. You have a record in BIC_Inter_TestDataBase_QA.dbo.city that is new (does not match one in CityDimension table). BIC_Inter_TestDataBase_QA.dbo.city. ctid is NULL.
  5. You have a record in BIC_Inter_TestDataBase_QA.dbo.city that is new (does not match one in CityDimension table). BIC_Inter_TestDataBase_QA.dbo.city. sid is NULL.

I would also think of some reasonable invalid scenarios to test the behavior of the PopulateCityDimension under those conditions. For example what happens when the data in BIC_Inter_TestDataBase_QA.dbo.city comes with a sid that is not present in StateDimension table. Clarify how the procedure is supposed to behave and then define a clear test scenario to validate that behavior.

The test procedure for scenario 1. would be something like:

CREATE PROCEDURE SQLTest_PopulateCityDimension_InsertUnknownCity
AS
BEGIN

   DECLARE @UnknownCityEntries int

   -- Set-up the condition that we want to test: 
   -- The CityDimension does not have the UNKNOWN entry
   DELETE FROM dbo.CityDimension WHERE CityName='UNKNOWN' AND CityID='UNKNOWN'

   -- Now call the PopulateCityDimension sproc that is supposed to insert such row.
   EXEC dbo.PopulateCityDimension

   -- Now validate the fact that PopulateCityDimension did indeed do its job
   SELECT @UnknownCityEntries = COUNT(*) FROM dbo.CityDimension Where CityName='UNKNOWN' AND CityID='UNKNOWN'
   EXEC TST.Assert.Equals 'There should be exactly one unknown entry in CityDimension', 1, @UnknownCityEntries

END
GO

The test procedure for scenario 3) would be something like:

CREATE PROCEDURE SQLTest_PopulateCityDimension_InsertNewCities
AS
BEGIN

   DECLARE ...    -- declare here all the variables used

   -- Set-up the condition that we want to test: 
   -- The table in BIC_Inter_TestDataBase_QA.dbo.city has some new entries that are not found in CityDimension 

   SELECT @NewCityId = MAX(Convert(INT,CityID)) + 1 FROM dbo.CityDimension WHERE CityID <> 'UNKNOWN'
   SELECT @ValidStateId1 = ...   -- Set this variable with a valid state id from statedimension
   SELECT @ValidCountryId1 = ... -- Set this variable with a valid state id from countrydimension
   SELECT @ValidStateId2 = ...   -- Set this variable with a valid state id from statedimension
   SELECT @ValidCountryId2 = ... -- Set this variable with a valid state id from countrydimension
   INSERT INTO BIC_Inter_TestDataBase_QA.dbo.city(cid, [sid], ctid, cName) VALUES (@NewCityId, @ValidStateId1, @ValidCountryId2, 'some new city')
   INSERT INTO BIC_Inter_TestDataBase_QA.dbo.city(cid, [sid], ctid, cName) VALUES (@NewCityId + 1, @ValidStateId1, @ValidCountryId2, 'another new city')

   -- Store the count of entries in CityDimension before calling PopulateCityDimension
   SELECT @CityCountBefore = COUNT(*) FROM dbo.CityDimension 

   -- Now call the PopulateCityDimension sproc that is supposed to transfer the new rows 
   EXEC dbo.PopulateCityDimension

   -- Now validate the fact that PopulateCityDimension did indeed insert the first city.
   SELECT @City1Count = COUNT(*) FROM dbo.CityDimension 
   WHERE CityID = CONVERT(NVARCHAR, @NewCityId) AND StateKeyID = CONVERT(NVARCHAR, @ValidStateId1) AND CountryKeyID = CONVERT(NVARCHAR, @ValidCountryId1) AND CityName = 'some new city'
   EXEC TST.Assert.Equals 'There should be exactly one entry in CityDimension corresponding to "some new city"', 1, @City1Count

   -- Now validate the fact that PopulateCityDimension did indeed insert the second city.
   SELECT @City2Count = COUNT(*) FROM dbo.CityDimension 
   WHERE CityID = CONVERT(NVARCHAR, @NewCityId + 1) AND StateKeyID = CONVERT(NVARCHAR, @ValidStateId2) AND CountryKeyID = CONVERT(NVARCHAR, @ValidCountryId2) AND CityName = 'another new city'
   EXEC TST.Assert.Equals 'There should be exactly one entry in CityDimension corresponding to "another new city"', 1, @City2Count

   -- Make sure that PopulateCityDimension onlyinserted two new entries and not more
   SELECT @CityCountAfter = COUNT(*) FROM dbo.CityDimension 
   SET @CityCountDifference = @CityCountAfter - @CityCountBefore
   EXEC TST.Assert.Equals '(Count after - Count before) must be 2', 2, @CityCountDifference

END
GO

I did not compiled / run these since I don't have your DBs so you may need to correct some syntax issues...

Assuming you create these two sprocs in BIRetail_TestDataBase (you can create them in a separate DB) you can run the test in cmd prompt:
TST  /RunAll  BIRetail_TestDataBase


One more note.
You must also figure out some logistics. Do you have total control of the database / tables you use to test your sprocs? If yes, then things are simpler. Make sure that you start from a state where they have no data or a minimal amount of data. It maybe that you don’t have as much control and you have to write your test assuming that the databases already have data that is not under your control. What situation you find yourself in may change the way you write some of the test code.