Whats a good solution to dynamically build 'AN

问题:

Currently our application has a need for a query to return a result that meets two desired requirements. For example, we are looking for a Person who is both a Project Manager AND a Business Analyst.

To pass in the list to this stored procedure I am creating a Table of Id's that can be accessed in the SQL statement. Below is my code attempting to build and execute the query

So far I have been unable to come up with a successful solution to dynamically building a query string that uses the AND operator between the Id's in the table parameter passed into the stored procedure.

For testing purposes I have hard coded elements into the occupation table.

BEGIN
DECLARE @SQLQuery NVARCHAR(MAX)
SET @SQLQuery = N'
SELECT DISTINCT
    Candidate.PersonId,
    Person.Name
FROM
    Candidate
INNER JOIN Person on Candidate.PersonId = Person.Id
    LEFT OUTER JOIN PersonOccupation on Candidate.PersonId = PersonOccupation.PersonId
    LEFT OUTER JOIN Occupation on PersonOccupation.OccupationId = Occupation.Id
'

--SELECTS ACTIVE CANDIDATES
SET @SQLQuery = @SQLQuery + 'WHERE Candidate.IsActive = 1
'

--QUERY FOR OCCUPATIONLIST TABLE

DECLARE @OccupationAndListTable dbo.IdList_TableType,
        @RowCount INT,
        @CurrentRow INT,
        @OccupationId INT

INSERT INTO @OccupationAndListTable
SELECT 3
INSERT INTO @OccupationAndListTable
SELECT 12

IF EXISTS(SELECT * FROM @OccupationAndListTable)
BEGIN
select * From @OccupationAndListTable
set @RowCount = @@ROWCOUNT
set @CurrentRow = 0

CREATE TABLE #TempOccupation (
                IdRowCount int IDENTITY(1,1) NOT NULL,
                Id int)

    INSERT INTO #TempOccupation
    SELECT * FROM @OccupationAndListTable

While @CurrentRow < @RowCount
BEGIN
select @OccupationId = Id 
from #TempOccupation o
where o.IdRowCount = @CurrentRow

    set @CurrentRow = @CurrentRow + 1
    PRINT CAST (@CurrentRow as VARCHAR)
    PRINT CAST (@OccupationId as VARCHAR)
    SET @SQLQuery = @SQLQuery + ' AND PersonOccupation.OccupationId =  CAST(@OccupationId as NVARCHAR) '

END
END

--SETS THE ORDER BY TO PERSON'S NAME
SET @SQLQuery = @SQLQuery + ' 
ORDER BY 
    Person.Name 
'



PRINT @SQLQuery


EXEC sp_executesql @SQLQuery,
N' @OccupationAndListTable dbo.IdList_TableType READONLY',
@OccupationAndListTable;

END

DROP TABLE #TempOccupation

If anyone can post a solution or point me into the direction to a solution to this problem that would be great. I've been struggling with this for too long now.

Thanks, Mike

回答1:

Not sure why you are using dynamic SQL for this query - you don't need to!

DECLARE @OccupationAndListTable dbo.IdList_TableType
INSERT INTO @OccupationAndListTable
SELECT 3
UNION
SELECT 12

DECLARE @occupationCount INT
SELECT @occupationCount = COUNT(1) FROM @OccupationAndListTable 

;WITH po AS (
   SELECT 
       PersonOccupation.PersonId,
       COUNT(1) AS OccupationCount
   FROM PersonOccupation
       JOIN @OccupationAndListTable AS olt ON PersonOccupation.OccupationId = olt.Id
   GROUP BY PersonOccupation.PersonId
)
SELECT
    Candidate.PersonId,
    Person.Name
FROM
    Candidate
    INNER JOIN Person on Candidate.PersonId = Person.Id
    LEFT JOIN po ON Person.Id = po.PersonId
WHERE Candidate.IsActive = 1
    AND ISNULL(po.OccupationCount,0) = @occupationCount

回答2:

I'd rather go with a reletional approach and I would avoid dynamic SQL.

Something like this:

DECLARE @OccupationAndListTable dbo.IdList_TableType,
        @RowCount INT,
        @CurrentRow INT,
        @OccupationId INT

INSERT INTO @OccupationAndListTable
SELECT 3
INSERT INTO @OccupationAndListTable
SELECT 12

SELECT DISTINCT Candidate.PersonId
               ,Person.Name
FROM Candidate
INNER JOIN Person 
    ON Candidate.PersonId = Person.Id
LEFT OUTER JOIN PersonOccupation 
    ON Candidate.PersonId = PersonOccupation.PersonId
LEFT OUTER JOIN Occupation 
    ON PersonOccupation.OccupationId = Occupation.Id
INNER JOIN @OccupationAndListTable AS OLT
    ON OLT.Id = PersonOccupation.OccupationId
WHERE Candidate.IsActive = 1
ORDER BY Person.Name