SQL Server 2008 SMS 与 Visual Studio 2010 服务器资源管理器结果

Question

我在 Sql Server 2008 Express SMS 中编写并在 Visual Studio 2010 中使用的 SQL 查询出现问题。

基础知识：网页上有一个输入框。它用于搜索人名、地址、ID 等。当我在网站上或内置 Visual Studio Server Explorer 中使用人名时，例如“Kargul”（编造的），它不会使用我的存储过程不会返回任何结果。 但是，在 SQL Server 2008 Express SMS 中，相同的存储过程返回有效结果。

我有什么遗漏的吗？这是我的程序：

SET NOCOUNT ON;

DECLARE @vars NVARCHAR(50);
SET @vars = '%' + @searchTerm + '%';

SELECT a.PersonId, Stat.StatusId, a.Honorific, a.FirstName, a.LastName, a.Street1, a.Street2, a.City, a.State, a.Zip, a.Phone, a.Email FROM Persons AS a
JOIN dbo.PersonStatus as Stat
ON a.StatusId = Stat.StatusId
    WHERE
        a.FirstName LIKE (@vars)
        UNION
        SELECT a.PersonId, Stat.StatusId, a.Honorific, a.FirstName, a.LastName, a.Street1, a.Street2, a.City, a.State, a.Zip, a.Phone, a.Email FROM Persons AS a
            JOIN dbo.PersonStatus as Stat
            ON a.StatusId = Stat.StatusId
                WHERE
        a.LastName LIKE (@vars)
        UNION
        SELECT a.PersonId, Stat.StatusId, a.Honorific, a.FirstName, a.LastName, a.Street1, a.Street2, a.City, a.State, a.Zip, a.Phone, a.Email FROM Persons AS a
            JOIN dbo.PersonStatus as Stat
            ON a.StatusId = Stat.StatusId
                WHERE
        a.Street1 LIKE (@vars)
        UNION
        SELECT a.PersonId, Stat.StatusId, a.Honorific, a.FirstName, a.LastName, a.Street1, a.Street2, a.City, a.State, a.Zip, a.Phone, a.Email FROM Persons AS a
            JOIN dbo.PersonStatus as Stat
            ON a.StatusId = Stat.StatusId
                WHERE
        a.City LIKE (@vars)
        UNION
        SELECT a.PersonId, Stat.StatusId, a.Honorific, a.FirstName, a.LastName, a.Street1, a.Street2, a.City, a.State, a.Zip, a.Phone, a.Email FROM Persons AS a
            JOIN dbo.PersonStatus as Stat
            ON a.StatusId = Stat.StatusId
                WHERE
        a.PersonId LIKE (@vars)
        UNION
        SELECT a.PersonId, Stat.StatusId, a.Honorific, a.FirstName, a.LastName, a.Street1, a.Street2, a.City, a.State, a.Zip, a.Phone, a.Email FROM Persons AS a
            JOIN dbo.PersonStatus as Stat
            ON a.StatusId = Stat.StatusId
                WHERE
        a.State LIKE (@vars)
    ORDER BY PersonId;

Answer 1

在服务器上运行探查器跟踪，以查看在 VS2010 中运行时正在执行什么；是否有可能搜索参数没有被传递？或者也许您需要在您的过程中设置 SET NOCOUNT ON。