TSQLT从存储过程返回结果 [英] TSQLT Returning Results from a Stored Procedure

问题描述

在TSQLT中，我试图从存储过程中返回结果并将其添加到变量中，以便可以断言它是否与预期结果相符.

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?

预先感谢

推荐答案

如果要从存储过程中获取变量，一种方法是使用out参数

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

如果您从存储过程中得到了一个结果集，这是我编写的tSQLt测试的一个示例.我没有为整个测试而烦恼，因为这应该可以为您提供所需的内容.

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    

这里的窍门是必须首先创建#actual表.它应包含与存储过程返回的列相同的列.

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.

顺便说一句，您可能已经注意到我在#actual表中有一个SortOrder列.这是因为我有兴趣测试针对此特定报告返回的数据的顺序. EXEC tSQLt.AssertEqualsTable将匹配像like这样的行，但不匹配行在预期行和实际行中出现的顺序，因此确保顺序的方法是在两个行之间添加一个SortOrder列(这是一个标识列). #expected和#actual

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

这篇关于TSQLT从存储过程返回结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！