Post reply

Cursor Versur Temporary Table

  • August 23, 2005 at 8:24 am

    #89517

    Hi All,

    I am writing a stored procedure in which I need to read some records from a  table which satisfy given condition, fetch the last read record and check its value.

    e.g.

    SELECT *

    FROM TestRequestState

    WHERE StateId = '11'

    I want to read the retuned recordset's last row and check some values from that row. For this I was thiking of reading the above Select using a cursor and then using FETCH LAST to read the last row into some variable.

    But this seems to be a round about way and also i have been reading that cursor will slow the execution. IS there any other better way. Should I use temp table instead, will that be more efficient ?

    Please let me know your suggestions.

    Thanks,

    Snigdha

  • August 23, 2005 at 8:40 am

    #584212

    Just add  NMB of Variable =as many FIELDS you have and do a

     select var1=field1, var2=field2 ...

    just like in this example

    declare @Tmp table (a int,b int identity(1,1))

    insert into @Tmp

    select 1 union all

    select 2 union all

    select 1 union all

    select 3 union all

    select 1 union all

    select 1

    declare @intA int

    declare @intB int

    select @intA=a,@intB=b

    from @Tmp where a=1

    select * from @Tmp

    select @intA,@intB

    Kindest Regards,

    Vasc

  • August 23, 2005 at 9:01 am

    #584229

    wow...

    that was the easiest way to do. It was so easy that i took some time to understand it..)

    Thanks a lot,

    Snigdha

  • August 23, 2005 at 9:47 am

    #584264

    I forgot to mention one more condition...

    My first Select gives me a set of records, I need to order them onthe TestRequestNo and then take the last of all the TestRequestNo.

    Now, how do I do this ??

    Snigdha

  • August 23, 2005 at 9:55 am

    #584270

    select top 1 *

    from TestRequestState

    WHERE StateId = '11'

    ORDER BY TestRequestNO DESC

    Kindest Regards,

    Vasc

  • August 24, 2005 at 6:59 am

    #584559

    The above query returns the last record of the last TestRequestNO. But I wanted to the last record of each TestRequestNO. For e.g.:

    TestRequestNo  TestRequestStateId

    TR123  11

    TR123  3

    TR123  5

    TR123  12

    TR155  11

    TR155  3

    TR155  5

    TR007  11

    TR007  12

    TR007  3

    Now, out of this orderd set of TestRequestNos I want the last record of each of the TestRequestNo: TR123, TR155, TR007.

    Thanks,

    Snigdha

     

  • August 24, 2005 at 7:03 am

    #584562

    Here's an exemple of this technic :

    SELECT O.XType

    , O.name

    FROM dbo.SysObjects O

    WHERE ID IN (SELECT TOP 90 PERCENT ID FROM dbo.SysObjects O2 WHERE O2.XType = O.XType order by O2.Name)

    ORDER BY O.XType, O.Name

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply