<div>
&lt; My above comment was changed a couple of times after initial post &gt;
</div>


<div>
I couldn't use &quot;sp_executesql @sql&quot;. I got &quot;Failed: 102 Incorrect syntax near 'sp_executesql'<br />
<br />
I then tried &quot;exec(@sql)&quot; and got, <br />
<br />
Select Failed: 1087 Must declare the table variable &quot;@wkActiveContrib&quot;.<br />
<br />
Kind of makes sense since the @wkActiveContrib table is probably not in scope in a spawned exec.
</div>


<div>
OK, got it to work with temp table: #wkActiveContrib. Need to do one more test ...
</div>


<div>
&gt;Select Failed: 1087 Must declare the table variable &quot;@wkActiveContrib&quot;.<br />
Since I don't see that variable in the original question I would guess that that's an error somewhere else in your SP that you'll have to address. &nbsp; I recommend double-clicking on the error message and eyeballing the cursor as it jumps to the offending line.
</div>


<div>
hmmm, doesn't work with order by:<br />
<br />
delete top (21) from #wkActiveContrib &nbsp;where contribYear = 2007 order by payDate<br />
<br />
Select Failed: 156 Incorrect syntax near the keyword 'order'.
</div>


<div>
Probably an issue with DELETE and TOP that doesn't allow an ORDER BY, and using the CTE by SELECTing it in a subquery then deleting in the main query is a work-around.
</div>


<div>
I didn't try Anthony's and Arif's solutions, but they do look viable. Thx!
</div>


<div>
<div class="content wysiwyg-content">
I have a stored procedure and want to delete a number of rows than can vary:<br />
<br />
declare @rowsToDelete int<br />
set @rowsToDelete = 20<br />
<br />
delete top @rowsToDelete from @myTable where someColumn = 'this' order by someOtherCol asc<br />
<br />
This doesn't work. I get: &quot;Select Failed: 102 Incorrect syntax near '@rowsToDelete'.<br />
<br />
Is there a way?
</div>
</div>


I have a stored procedure and want to delete a number of rows than can vary:

declare @rowsToDelete int
set @rowsToDelete = 20

delete top @rowsToDelete from @myTable where someColumn = 'this' order by someOtherCol asc

This doesn't work. I get: "Select Failed: 102 Incorrect syntax near '@rowsToDelete'.

Is there a way?

how to delete a variable number of rows?

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.