You may ran this query,

Just change the name of the table: [dbo].[VB8_DA] with yours.

or call it from SSRS

create Procedure dbo.VendorReport

(@VendorOrders varchar(10),

@LimitToTopParameter int,

@Flag int

)

As

Begin

--if @Flag=1 mean NULL checkbox checked

declare @SQL nvarchar(max)

declare @SQLtop nvarchar(max)

if isnull(@LimitToTopParameter, 0)>0 and @Flag = 1

begin

print ' You cannot use both LimitToTopParameter and Checkbox checked as same time'

return

end

--If I select Top in that , then i should enter "Limit to top parameter" parameter value is 10 or give any value then

--should display 10 or given value rows of Top or

If (@VendorOrders='Top' and isnull(@LimitToTopParameter, 0)>0 and @Flag <> 1)

begin set @VendorOrders='' end

--If i select Top in that , then i was not entered any value then i checked check box is null it should returns for Descending order of the data

else If (@VendorOrders='Top' and isnull(@LimitToTopParameter, 0)=0 and @Flag = 1)

begin set @VendorOrders='Desc' end

--If i select Bottom then i should enter "Limit to top parameter" parameter value is 10 or give any value then should display the Bottom 10 or given values rows of Bottom to the data set.

If (@VendorOrders='Bottom' and isnull(@LimitToTopParameter, 0)>0 and @Flag <> 1)

begin set @VendorOrders='Desc' end

--If i select Bottom in that , then i was not entered any value then i checked check box is null it should returns for Ascending order of the data.

else If (@VendorOrders='Bottom' and isnull(@LimitToTopParameter, 0)=0 and @Flag = 1)

begin set @VendorOrders='Asc' end

if @LimitToTopParameter is null

begin set @SQLtop='' end

else

begin Set @SQLtop='TOP '+cast(@LimitToTopParameter as varchar(100)) end

set @sql='SELECT '+@SQLtop+' * FROM [dbo].[VB8_DA] order by 1 '+@VendorOrders

print @SQL

exec sp_executeSQL @SQl

end

exec dbo.VendorReport 'Top' , 10, 0

exec dbo.VendorReport 'Top' , NULL , 1

exec dbo.VendorReport 'bottom' , 10, 0

exec dbo.VendorReport 'bottom' , NULL, 1