Variable Select Top Specification

[Signit]

I am trying to dynamically specify the number of records to pull out from a table. Prior to this statement I have generated a variable number into @selection_size. The syntax below does not compile in a stored proc. Is there a means of accomplishing this?

select top @selection_size
  from wfa_log a
 where a.location_bldg_num = @location_bldg_num
   and a.audit_customer_complete is null

 

[Signit]

Ended up with the below code after some more research. Is there a better/more efficent way of doing this? Thanks!

      set rowcount @selection_size
      
      select *
        from wfa_log a
       where a.location_bldg_num = @location_bldg_num
         and a.audit_customer_complete is null

 

[Savil]

I tried every method of this and the only way I could get it to work was using sp_executesql

CREATE PROCEDURE pr_DrillDownRecSource
@IPTID tinyint, @ActivityCat tinyint, @strWhere VarChar(1000) = '', @TopWhat VarChar(25) = '', @StrOrder VarChar(50) = '' AS

DECLARE @strSQL nvarchar(4000)

SET @strSQL= N'SELECT ' + @TopWhat + '
PartNo,What_If_Remind_Date,Reviewed_Status_ID,Reviewed_Status_Date,Alternative_Count,

FROM tbl_Calc_Metrics_Reps a
INNER JOIN tbl_Base_Data b ON
b.NSN = a.NSN
WHERE a.IPT_Id = @IPT AND ActivityCat = @AC ' + @StrWhere + @StrOrder

DECLARE @Params nvarchar(25)

EXECUTE sp_executesql @strSQL, @Params = N'@IPT tinyint, @AC tinyint', @IPT = @IPTID, @AC = @ActivityCat

hope this helps