Please Help

12 Aug 2005 4:38 PM

A.B.

I am trying to pull the last 30 records in a table. I'm trying to write a
dynamic stored procedure in order to do this. I am joining a couple tables
and the table i need to get the 30 records out of is the second table. I
can't seem to get it to work with my code. Below is the code that I am using
to try to do this, I have declared the variables earlier in this procedure:

(Select Distinct LotID, @LowNum = max(LotID)-30, @HighNum = max(LotID)
            From dbo.schedulegovcom
            Where LotID<=@HighNum
                and >=@LowNum
            Group by LotID) p

12 Aug 2005 4:43 PM

Mike Jansen

Try:

SELECT TOP 30
    [field list]
FROM
    dbo.schedulegovcom
ORDER BY
    LotID DESC

Mike

Show quote

"A.B." <A*@discussions.microsoft.com> wrote in message
news:F449DAD0-7B92-4E1A-9676-1604C2D79D3A@microsoft.com...
>I am trying to pull the last 30 records in a table. I'm trying to write a
> dynamic stored procedure in order to do this. I am joining a couple tables
> and the table i need to get the 30 records out of is the second table. I
> can't seem to get it to work with my code. Below is the code that I am
> using
> to try to do this, I have declared the variables earlier in this
> procedure:
>
> (Select Distinct LotID, @LowNum = max(LotID)-30, @HighNum = max(LotID)
> From dbo.schedulegovcom
> Where LotID<=@HighNum
> and >=@LowNum
> Group by LotID) p

12 Aug 2005 5:31 PM

A.B.

Thanks that worked

Show quote

"Mike Jansen" wrote:

> Try:
>
> SELECT TOP 30
>     [field list]
> FROM
>     dbo.schedulegovcom
> ORDER BY
>     LotID DESC
>
> Mike
>
> "A.B." <A*@discussions.microsoft.com> wrote in message
> news:F449DAD0-7B92-4E1A-9676-1604C2D79D3A@microsoft.com...
> >I am trying to pull the last 30 records in a table. I'm trying to write a
> > dynamic stored procedure in order to do this. I am joining a couple tables
> > and the table i need to get the 30 records out of is the second table. I
> > can't seem to get it to work with my code. Below is the code that I am
> > using
> > to try to do this, I have declared the variables earlier in this
> > procedure:
> >
> > (Select Distinct LotID, @LowNum = max(LotID)-30, @HighNum = max(LotID)
> > From dbo.schedulegovcom
> > Where LotID<=@HighNum
> > and >=@LowNum
> > Group by LotID) p
>
>
>

12 Aug 2005 4:50 PM

Alejandro Mesa

You can not mix resultset with assigning value to a variable in the same
select statement.

-- wrong
select @i = orderid, customerid from dbo.orders

AMB

Show quote

"A.B." wrote:

> I am trying to pull the last 30 records in a table. I'm trying to write a
> dynamic stored procedure in order to do this. I am joining a couple tables
> and the table i need to get the 30 records out of is the second table. I
> can't seem to get it to work with my code. Below is the code that I am using
> to try to do this, I have declared the variables earlier in this procedure:
>
> (Select Distinct LotID, @LowNum = max(LotID)-30, @HighNum = max(LotID)
>             From dbo.schedulegovcom
>             Where LotID<=@HighNum
>                 and >=@LowNum
>             Group by LotID) p