How to return a specified number of results in T-SQL

[elmo29]

<br>I am writing a program in VB6 and SQL 7.&nbsp;&nbsp;Customers can request a batch of phonenumbers.&nbsp;&nbsp;Depending on the number they type in will depend on how many phone numbers will be reserved.<br><br>I want to send the number to batch as an input parameter and then return the top phonenumbers to insert into my reseved table (phonenumbers must be given out in order)<br><br>However when I try the following it doesn't work.<br><br>I have even tried putting it as a string and then exec(the string) and this still doesn't work.&nbsp;&nbsp;I am given no error message in both cases, but if I convert it into a view it works (ie take out the parameters).<br><br>Any ideas?? Please? :O)<br><br>INSERT le0reserved<br>SELECT TOP @batchnumber phonenumber, @customer AS CustomerID, getdate() <br>&nbsp;&nbsp;&nbsp;&nbsp;AS CurrentDate<br>FROM le0pho, le0tariff<br>WHERE le0pho.tarifftype = le0tariff.tariffid AND le0tariff.numbertype = @tariff AND avaliable = 1<br>order by phonenumber<br><br>** OR **<br><br>EXEC sp_dboption 'Telsis', 'quoted identifier', TRUE<br><br>SET @LString = 'INSERT le0reserved '<br>SET @LString = @LString + ' SELECT top ' + CONVERT(nvarchar,@batchnumber)<br>SET @LString = @LString + ' phonenumber, ' + @customer + ' As CustomerID, ' + getDate() + ' AS CurrentDate '<br>SET @LString = @LString + '&nbsp;&nbsp;FROM le0pho, le0tariff '<br>SET @LString = @LString + '&nbsp;&nbsp;WHERE (avaliable - 1 AND le0pho.tarifftype = le0tariff.tariffid) '<br>SET @LString = @LString + ' AND le0tariff.numbertype = ''' @tariff '''

 

[hirick]

SQL requires an integer following the TOP keyword.
You have two choices.

1) create the SQL as a string, and execute the statement rather than use a stored procedure

2) Use the Rowcount instead. Make sure to set the rowcount back to zero at the end of the sproc, otherwise it will continue to be the maximum for any other procedure in the session. Here's a sample.

Create Procedure TestRowcount

(@rowcount int = 5)

As
/* set nocount on */
set rowcount @rowcount
select * from SHPlanInfo
set rowcount 0
return