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
4 3853 almaz 168
Recognized Expert New Member - ;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
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_Sta tus, Line 45
Incorrect syntax near the keyword 'SET'.
almaz 168
Recognized Expert New Member ;with Ordered(temp_va lue, Position) as
(
select temp_value, Position = row_number()
over(order by temp_date desc)
from temp_sensor_tab le
where device_id = '1'
)
select @temp_value = temp_value
from Ordered
where Position = 1
Thank you very much for your help, it is working now
NormannTheDane
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Robbie |
last post by:
Hi All
This is a belter that my little brain can't handle.
Basically I have 1 SQL table that contains the following fields:
Stock Code
Stock Desc
Reference
Transaction Date
|
by: ImraneA |
last post by:
Hi there
Application : Access v2K/SQL 2K
Jest : Using sproc to append records into SQL table
Jest sproc :
1.Can have more than 1 record - so using ';' to separate each line
from each other.
|
by: siatki |
last post by:
Hello,
Firstly, sorry for my english.
I have problem with creating SQL statement. I am beginner and I think that
it is very easy to do. Look -
I have to get only the last 20 records from table ABC (f.eg.) and according
to that how to create the SQL statement? F.eg. SELECT name, forename, date
FROM abc WHERE....
You know - how to finish the statement to get only 20 records lastly
inserted into the table?
Thanks and I am waiting for...
|
by: Ray via SQLMonster.com |
last post by:
Hi there,
See if you can help me with the following:
I need to write an SQL code that will return me:
The 1st day & the Last day of the Previous Month in the following format
(smalldatetime):
yyyy-mm-dd hh:mi:ss (24h)
|
by: mgarriss |
last post by:
Given this table:
CREATE TABLE test ( id SERIAL, example TEXT );
An implicit sequence is created as show in this message:
NOTICE: CREATE TABLE will create implicit sequence 'test_id_seq' for
SERIAL column 'test.id'
How do I retrieve the last 'id' that was inserted? I have a process
| |
by: Mark |
last post by:
I'm using ASP.Net to accress a database, what I need to do is get the fields
out of the very last record in the db.
How do I do this?
Actually I'm after the primary key, titled 'AdID' it'll tell me what number
this last record is. so I can tell what number the next record inserted will
be.
How do I do this?
Thanks,
|
by: dbuchanan |
last post by:
Hello,
Here is the error message;
----------------------------
Exception Message:
ForeignKeyConstraint Lkp_tbl040Cmpt_lkp302SensorType requires the child
key values (5) to exist in the parent table.
----------------------------
|
by: SQL Server |
last post by:
I've been working this for a while. Kind of new to SQL Server
functions and not seeing what I am doing wrong. I have this function
CREATE FUNCTION dbo.test (@Group varchar(50))
RETURNS smalldatetime AS
BEGIN
Declare @retVal varchar(10)
(SELECT @retVal= MIN() FROM dbo.t_master_schedules WHERE
(event_id = 13) AND (group_ =@Group))
return convert(smalldatetime, @retVal, 1)
|
by: davydany |
last post by:
Hey guys...a n00b Here for this site.
I'm making a sequence class for my C++ class. And The thing is in the array that I have, lets say i put in {13,17,38,18}, when i see the current values for the array data from 0 to3, I get this {13, JUNK VALUE, 17,38, 18} and JUNK VALUE is like 1.8e831 or something like that.
This happens when I use the attach() function and use the current() function to display the values at data
I really want to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |