TSQLT Returning Results from a Stored Procedure

In TSQLT, I'm trying to return a result from a stored procedure and add it to a variable so that I can assert if it matches my expected result.

I've seen loads of examples of returning results from functions but none where a stored procedure is called.

Does anybody have examples that they could share?

Thanks in advance

Solution

If you want to get a variable back from a stored procedure one way to do this is to use an out parameter

CREATE PROC MyTest
(@myVar int output)
AS
BEGIN
  SET @myVar = 10
END
GO
DECLARE @x int
EXEC MyTest @myVar=@x output
SELECT @x

If you are getting a result set back from the stored procedure, here is an example from a tSQLt test that I wrote. I haven't bothered with the whole test because this should give you what you need.

    CREATE TABLE #Actual (SortOrder int identity(1,1),LastName varchar(100), FirstName varchar(100), OrderDate datetime, TotalQuantity int) 
    -- Act  
    INSERT #Actual (LastName, FirstName, OrderDate, TotalQuantity)
    EXEC Report_BulkBuyers @CurrentDate=@CurrentDate

The trick here is that you have to create the #actual table first. It should contain the same columns as what is returned from the stored procedure.

Just as an aside, you may have noticed I have a SortOrder column in the #actual table. This is because I was interested in testing the order of the data returned for this specific report. EXEC tSQLt.AssertEqualsTable will match rows like for like, but does not match the order in which the rows appear in the expected and actual so the way to ensure the order is to add a SortOrder column (which is an identity column) to both the #expected and #actual