select top number of records

[orangeblue]

hi
i have a staement
set @freeitems=(@count/3)

and after that I want to use@freeitem for the below statement - at the moment i am getting an error saying
'Incorrect syntax near '@freeitems'.'

whats the best way to do this


select top @freeitems price from OrderItems where discount_id=@discount_id and order_id=@order_id order by price
thanks

 

[PHV]

If your DBMS supports it, you may try something like this:
SELECT O.price FROM (
select price from OrderItems
where discount_id=@discount_id and order_id=@order_id
order by price
) O WHERE O.rownum <= @freeitems


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[orangeblue]

thanks for that PH
but this is the whole query

set @freeitems=(@count/3)

select top 3 o.price from dbo.dbClient_OrderItems o inner join dbo.dbStock s on o.stock_id=s.stock_id where discount_id=@discount_id and order_id=@order_id order by o.price
how can i put the one you wrote on to my query

thanks

 

[swampBoogie]

The argument for top must be a constant so what you are attempting is not allowed.

You can either user

set rowcount @freeitems

or use dynamic sql. As this is specific to SQL server/sybase you are better of posting in a forum specific for those DBMS.