Using a TOP statment and dynamically Setting value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using a TOP statment and dynamically Setting value

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-07 : 14:56:36

I have the following query:

declare @PeriodToBill int, @GenerationDate datetime, @BilLCycleID int

Set @PeriodtoBill = 1
Set @GenerationDate = Getdate()
Set @BilLCycleID = 1

Select Top 1 b.GenerationDate From
(Select Top @PeriodtoBill a.GenerationDate
From TAC_GenerationDate a
where a.GeneartionDate <= @GenerationDate and a.BillingCycleID = @BillCycleID order by a.GenerationDate asc) b

I need the value of @PeriodtoBill as the number of rows to return as in "SELECT TOP @PERIODTOBILL a.GenerationDate", but T-sql doesn't like that, so I figured I'd simply conver the whole query to a string and run it as in

exec('Select Top 1 b.GenerationDate From
(Select Top ' + @PeriodtoBill + ' a.GenerationDate
From TAC_GenerationDate a
where a.GeneartionDate <= ''01/01/2006'' and a.BillingCycleID = ' + @BillCycleID + ' order by a.GenerationDate asc) b')

Still does not work, and I get a error saying something along the lines of I can not use a time dependent variable in a exec.

Thanks.

MichaelP
Jedi Yak

2489 Posts

Posted - 2006-08-07 : 16:57:59

You need to use SET ROWCOUNT to do a dynamic row count.


DECLARE @NumRows INT

SET @Numrows = 10

SET ROWCOUNT @NumRows

Select statement here

SET ROWCOUNT 0

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-07 : 17:18:55

I run into the exact same issue if I use

SET ROWCOUNT @PeriodtoBill

this is to be used in a function, so I am passing that variable.

Maybe I am not using the right approach. This is what I am looking to accomplish

Create Table #T1(GenerationDate DateTime)

Insert Into #T1(GererationDate)
Select '01/01/2005' UNION ALL
Select '02/01/2005' UNION ALL
Select '03/01/2005' UNION ALL
Select '04/01/2005' UNION ALL
Select '05/01/2005' UNION ALL
Select '06/01/2005' UNION ALL
Select '07/01/2005'

DECLARE @Num int, @Dt datetime

I want to say give me the date that is @Num less then @Dt
so if I was to pass the @NUM var 3 and give the Date '06/01/2005'
I would get '03/01/2005' because it's the 3rd record less then the date I passed.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-07 : 17:58:16

I was able to solve my issue using the following

declare @TT table(RowNum int,d datetime)
Declare @RowNum int, @ADJ int
set @Adj = 1

insert into @TT(RowNum,d)
select ROW_NUMBER() OVER (Order By A.GenerationDate) as RowNum
,a.GenerationDate
From TAC_GenerationDate a

select a.d
From @TT a
where a.RowNum = (select top 1 a1.RowNum from @TT a1
order by a1.rownum desc) - @Adj

If someone can think of a better way to do this, please let me know.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-08 : 13:13:07

In sql server 2000, finding row number and matching with that may be ineffecient if there are thousands of rows in the table

Madhivanan

Failing to plan is Planning to fail

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-08 : 14:19:19

I am open to sugestions.. I modified the query so I am only dealing with approx 1000 records at max, so hopfully performance will not be a issue.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources