Solved

Top Percent with varable

Posted on 2007-11-16
9
748 Views
Last Modified: 2012-08-14
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()) > (@EldestCrit * 12)

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...
0
Comment
Question by:MadIce
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 31

Expert Comment

by:James Murrell
ID: 20301781
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20301782

in sql 2000, you cannot do that with PERCENT, unless you would make it dynamic sql
0
 

Author Comment

by:MadIce
ID: 20301902
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
0
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20301920
unfortunately, you cannot use table variables inside dynamic sql unless they are declare altogether inside that same dynamic sql.

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20301955
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
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 20301967
Missed the ORDER BY clause it should be:

Set @SomeMaxValue = @@ROWCOUNT * @YourPercent / 100

Select *                               -- Replace * with your column names
From @YourTempTable
Where ID Between 1 And @SomeMaxValue
Order By ID

This way you avoid the whole dynamic SQL mess.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 20304083
use dynamic SQL...

--Stored procedure creation
create procedure spr_percent_query(@percentage smallint)
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()) > (@EldestCrit * 12)'
       exec (@string_execute1)
end
go

--Usage
exec spr_percent_query 20 --> top 20 percent

Hope this helps...
0
 

Author Closing Comment

by:MadIce
ID: 31409665
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
0
 

Author Comment

by:MadIce
ID: 20323150
Thanks, that's worked out fine.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question