MadIce
asked on
Top Percent with varable
I have the following query in a stored procedure:
insert into @tblTopTenPct
Select DLID = t1.DLID, Qty = t1.Qty, Base = t1.Base, xMonth = t1.xMonth, Issue = 'Old type'
FROM (SELECT TOP 10 Percent * FROM @DLList) AS t1
Where Datediff(mm,Base,GetDate()
Works fine. But now the users want to be able to change the percent at runtime
FROM (SELECT TOP 10 Percent * FROM @DLList) AS t1
My understanding is in Sql Server 2005 I can replace 10 percent with a varible @pct
But Im using Sql Server 2000.
I was trying to use dynamic sql but run in the problem with using variable tables. My thougt was to take the X percent a store it in a seperate table within my procedure and just select * in my from statement above.
Any Ideas...
insert into @tblTopTenPct
Select DLID = t1.DLID, Qty = t1.Qty, Base = t1.Base, xMonth = t1.xMonth, Issue = 'Old type'
FROM (SELECT TOP 10 Percent * FROM @DLList) AS t1
Where Datediff(mm,Base,GetDate()
Works fine. But now the users want to be able to change the percent at runtime
FROM (SELECT TOP 10 Percent * FROM @DLList) AS t1
My understanding is in Sql Server 2005 I can replace 10 percent with a varible @pct
But Im using Sql Server 2000.
I was trying to use dynamic sql but run in the problem with using variable tables. My thougt was to take the X percent a store it in a seperate table within my procedure and just select * in my from statement above.
Any Ideas...
James Murrell
have you read https://www.experts-exchange.com/questions/22420535/passing-a-value-from-an-access-textbox-to-sqlserver-view.html?sfQueryTermInfo=1+percent+varabl
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
in sql 2000, you cannot do that with PERCENT, unless you would make it dynamic sql
ASKER
I tried this:
set @sql = 'Insert into @TopPct Select Top ' + @EldestPercent + ' Percent * from @DLList'
Execute (@Sql)
@TopPct and @DLList' are tables and I dont think thats allowed in dynamic sql
set @sql = 'Insert into @TopPct Select Top ' + @EldestPercent + ' Percent * from @DLList'
Execute (@Sql)
@TopPct and @DLList' are tables and I dont think thats allowed in dynamic sql
unfortunately, you cannot use table variables inside dynamic sql unless they are declare altogether inside that same dynamic sql.
Your best bet is to add the entire resultset to a temporary table or variable of type table that has an IDENTITY column in the correct order. You can then easily calculate the percent based on the total number of rows added (@@ROWCOUNT) and so do something like this:
Set @SomeMaxValue = @@ROWCOUNT * @YourPercent / 100
Select * -- Replace * with your column names
From @YourTempTable
Where ID Between 1 And @SomeMaxValue
Set @SomeMaxValue = @@ROWCOUNT * @YourPercent / 100
Select * -- Replace * with your column names
From @YourTempTable
Where ID Between 1 And @SomeMaxValue
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
use dynamic SQL...
--Stored procedure creation
create procedure spr_percent_query(@percent
as
begin
declare@string_execute1 varchar(1000)
set @string_execute1 = 'insert into @tblTopTenPct
Select DLID = t1.DLID, Qty = t1.Qty, Base = t1.Base, xMonth = t1.xMonth, Issue = 'Old type'
FROM (SELECT TOP ' + @percentage + ' Percent * FROM @DLList) AS t1
Where Datediff(mm,Base,GetDate()
exec (@string_execute1)
end
go
--Usage
exec spr_percent_query 20 --> top 20 percent
Hope this helps...
--Stored procedure creation
create procedure spr_percent_query(@percent
as
begin
declare@string_execute1 varchar(1000)
set @string_execute1 = 'insert into @tblTopTenPct
Select DLID = t1.DLID, Qty = t1.Qty, Base = t1.Base, xMonth = t1.xMonth, Issue = 'Old type'
FROM (SELECT TOP ' + @percentage + ' Percent * FROM @DLList) AS t1
Where Datediff(mm,Base,GetDate()
exec (@string_execute1)
end
go
--Usage
exec spr_percent_query 20 --> top 20 percent
Hope this helps...
ASKER
Sorry for delay. Had a problem and wasn't sure of the cause. I was trying something simular when you posted your answer. answer gave me what I needed. Thanks
ASKER
Thanks, that's worked out fine.