SELECT Top (OuterQuery.Amount) * FROM

I have a problem dealing with the following construct:

SELECT *
FROM X
INNER JOIN
(
    SELECT TOP (X.Inte开发者_Go百科gerAmount) *
    FROM Y
) AS Z ON X.ID = Z.X_ID

Is there a solution for this problem?

You can't parameterise TOP with the value from another table

You can use a ranking function

SELECT *
FROM X
INNER JOIN
(
    SELECT ROW_NUMBER() OVER (ORDER BY SomeVal) as rn, *
    FROM Y
) AS Z ON X.ID = Z.X_ID
WHERE
   Z.rn <= X.IntegerAmount)

Or you'd have to make the derived query a UDF with a @topparameter

Note: you need an ORDER BY anyway

There are probably better ways to do what you actually want: this is answer to the what you actually asked which probably isn't the same thing...

EDIT:

I think i understand, try this:

DECLARE @i as INT = 10
SET @SQL + 'SELECT * FROM X INNER JOIN 
(     
    SELECT TOP 
    ' +CAST(@i AS NVARCHAR(10))  + ' *     
    FROM Y 
) AS Z     
ON     X.ID = Z.X_ID'
EXEC sp_executesql  @SQL