Getting the last inserted row by smalldatetime

[Normann]

I am creating a Stored Proc and I need to be able to select the last added row, now this should be made easier by the fact that I have a smalldatetime column in the table that is added every time a new row is inserted. I use MSSQL 2005 and the construction of the table is this:

CREATE TABLE temp_sensor_table
    (    
    temp_id Int Identity PRIMARY KEY,
    device_id Int NOT NULL
        REFERENCES Devices(device_id),
    temp_value NVARChar(10) NOT NULL,
    temp_date smalldatetime NOT NULL DEFAULT GETDATE()
    )
 

And the select statement that i have is this:

select temp_value 
from temp_sensor_table 
where device_id = @device_id_temp 
and temp_date = ???
 

The @device_id_temp is just an int that is selected in another select statement in the Stored Proc, what I need is the last part of the where statement.

NormannTheDane

[almaz]

;with Ordered(temp_value, Position) as
(
    select temp_value, Position = row_number() over(order by temp_date desc)
    from temp_sensor_table 
    where device_id = @device_id_temp 
)
select temp_value 
from Ordered 
where Position = 1

[Normann]

The select statement from above works perfectly, if it is on it's own but when I try to use it in the Stored Proc I get an error.

I think it is when I try to use it to set a parameter, but I might be wrong. I have include what I am trying to do, and I hope someone can spot what I am doing wrong.

CREATE PROCEDURE dbo.SP_Get_Data_Status
(
@room_name nvarchar(20),            --Input vari from C#
@temp_value nvarchar(4) output,            --Output vari to C#    
@humid_value nvarchar(4) output            --Output vari to C#
)
AS
    DECLARE @room_id int            
    DECLARE @device_type_id_temp int        
    DECLARE @device_type_id_humid int    --Vari used in 
    DECLARE @device_type_id_fire int       --the Stored Proc
    DECLARE @device_id_temp int        
    DECLARE @device_id_humid int        
 
 
SET @room_id = (                    --Gets the room ID 
select room_id from Rooms            --from Rooms Table
where room_name = @room_name)        --using @room_name
 
SET @device_type_id_temp = (            --Gets the type ID 
select device_type_id from Device_Type          --from Device_Type Table
where device_type_name = 'temp')        --using @room_id
 
SET @device_type_id_humid = (            --Gets the type ID
select device_type_id from Device_Type           --from Device_Type Table
where device_type_name = 'humid')        --using @room_id
 
SET @device_id_temp = (                       --Gets the device ID
select device_id from Devices                --from Devices Table
where room_id = @room_id and            --using @room_id &
device_type_id = @device_type_id_temp)        --@device_type_id_temp
 
SET @device_id_humid = (              --Gets the device ID
select device_id from Devices                --from Devices Table
where room_id = @room_id and            --using @room_id &
device_type_id = @device_type_id_humid)        --@device_type_id_humid
 
 
--Code from TheScripts.com forum By almaz Thread_id: 707431
;with Ordered(temp_value, Position) as        
(
select temp_value, Position = row_number() 
over(order by temp_date desc)
from temp_sensor_table
where device_id = '1'
)
SET @temp_value = (    --<-- Error happens here
select temp_value
from Ordered
where Position = 1)
 

When I try to execute it the error I recive is:

Msg 156, Level 15, State 1, Procedure SP_Get_Data_Status, Line 45
Incorrect syntax near the keyword 'SET'.

[almaz]

;with Ordered(temp_value, Position) as
(
select temp_value, Position = row_number()
over(order by temp_date desc)
from temp_sensor_table
where device_id = '1'
)
select @temp_value = temp_value
from Ordered
where Position = 1

[Normann]

Thank you very much for your help, it is working now

NormannTheDane