如何使用单个对象将QTP中的多个查询运行

Question

我有一个带有名为sqlqueries的列的Excel表。每个单元格都有一组查询。

我能够使用QTP运行sumple查询但是具有多个语句的单元，例如在细胞中（x，6）中存在以下查询：" 使用ldatabase exec sp_dropobjectifexists'#temptable'; 将Col1选择为#temptable可维护； 更新#temptable Set colv = 5 从#temptable中选择计数（1）作为总计"

以上只是一个而不是确切的SQL查询。整个集合都在一个Excel纸的单元格中。我希望使用QTP执行此操作。

目前，我在QTP中所做的是：

Set objconnection = CreateObject("ADODB.Connection")
objconnection.open"provider=blah blah blah"
 Set objrecordset= CreateObject("ADODB.Recordset")
 ws.cells(x,6).select ''''the above sql queries set is in this cell
Sqlquery1= ws.cells(x,6).value
objrecordset.Open Sqlquery1. objconnection
Value1=objrecordset.Fields.Item(0)

对于上面的最后一行，我会发现错误"项目在与RequestEf Name或Oldinal相对应的集合中找到"

我假设这是因为单个单元格中有多个语句要归咎于，但仅执行"使用ldatabase"的第一行。而不是所有的单元格内容。

您能帮我一击执行整个事情。

谢谢！

Answer 1

用 SET NOCOUNT ON;的查询前缀。这将使您可以在SQL语句中使用临时表和变量。

下面的代码反对这个。我已经使用了早期绑定来使代码易于阅读（工具>> 参考>>>> Microsoft ActiveX数据对象2.8库）。

在这些行之间切换以测试：

• rs.Open QueryA, cn, adOpenForwardOnly, adLockReadOnly
• rs.Open QueryB, cn, adOpenForwardOnly, adLockReadOnly

querya将失败。QueryB将返回Jack。

' Demo of using SET NOCOUNT ON;.
' This option enabled the use of SQL vars and temp tables.
Sub Test()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    cn.Open "Driver={SQL Server};Server=YOUR-SEVER-NAME-HERE;Database=master;Trusted_Connection=Yes;"
    ' QueryA fails, while QueryB does not.
    ' Switch which line is commented out to test.
    rs.Open QueryA, cn, adOpenForwardOnly, adLockReadOnly
    'rs.Open QueryB, cn, adOpenForwardOnly, adLockReadOnly
    ' This line will raise an error with QueryA.
    ' This line will work with QueryB.
    MsgBox rs.Fields(1).Value
    rs.Close
    cn.Close
End Sub
' Returns a sample query without NOCOUNT.
Public Function QueryA() As String
    QueryA = "              CREATE TABLE #ExampleA              "
    QueryA = QueryA & "     (                                   "
    QueryA = QueryA & "         Id      INT PRIMARY KEY,        "
    QueryA = QueryA & "         Name    VARCHAR(50) NOT NULL    "
    QueryA = QueryA & "     );                                  "
    QueryA = QueryA & ""
    QueryA = QueryA & "     INSERT INTO #ExampleA (Id, Name)    "
    QueryA = QueryA & "     VALUES                              "
    QueryA = QueryA & "         (1, 'Jack'),                    "
    QueryA = QueryA & "         (2, 'Jill')                     "
    QueryA = QueryA & "     ;                                   "
    QueryA = QueryA & ""
    QueryA = QueryA & "     SELECT * FROM #ExampleA             "
End Function
' Returns a sample query with NOCOUNT.
Public Function QueryB() As String
    QueryB = "              SET NOCOUNT ON;                     "
    QueryB = QueryB & ""
    QueryB = QueryB & "     CREATE TABLE #ExampleA              "
    QueryB = QueryB & "     (                                   "
    QueryB = QueryB & "         Id      INT PRIMARY KEY,        "
    QueryB = QueryB & "         Name    VARCHAR(50) NOT NULL    "
    QueryB = QueryB & "     );                                  "
    QueryB = QueryB & ""
    QueryB = QueryB & "     INSERT INTO #ExampleA (Id, Name)    "
    QueryB = QueryB & "     VALUES                              "
    QueryB = QueryB & "         (1, 'Jack'),                    "
    QueryB = QueryB & "         (2, 'Jill')                     "
    QueryB = QueryB & "     ;                                   "
    QueryB = QueryB & ""
    QueryB = QueryB & "     SELECT * FROM #ExampleA             "
End Function

我已经将查询的两个版本嵌入了几个丑陋的功能中。它们很难阅读，但很容易分享。以下是工作查询的干净版本。删除非工作变体的第一行。

SET NOCOUNT ON;
CREATE TABLE #ExampleA              
(                                   
    Id      INT PRIMARY KEY,        
    Name    VARCHAR(50) NOT NULL    
);                                  
INSERT INTO #ExampleA (Id, Name)    
VALUES                              
    (1, 'Jack'),                    
    (2, 'Jill')                     
;                                   
SELECT * FROM #ExampleA;