Return specific items from a single row as output parameters

Hello Experts,

Using SQL Server 2005...

Here is a simplified case of what I'm trying to do:

I have a table that contains time stamped data recorded from remote sensors.  I would like to have a single stored procedure that can return some aggregate data for for a given sensor over a given time frame.

I'm wondering how I query for ONE row, and then take a few of the fields' data and put it in output parameters.

Can I get a little help with the best way to accomplish this?  This would be psudo code for what I want to do...
CREATE PROCEDURE GetSensorSummary
   @SensorID int,
   @BegDate datetime,
   @EndDate datetime,
   @SensorDescription nvarchar(127) out,
   @MaxValue double out,
   @MaxTime datetime out,
   @MinValue double out,
   @MinTime datetime out
AS
BEGIN
   -- put the description from this query in @SensorDescription
   -- which I could do like this if only getting one value (I actually want to grab more than one):

   SET @SensorDescription = SELECT Description FROM SensorDef WHERE SensorID=@SensorID

   -- the queries below would also contain the date criteria in the WHERE clause (left out for brevity)

   SELECT TOP 1 DataValue, DataTime FROM SensorData WHERE SensorID=@SensorID ORDER BY DataValue DESC
   -- want to set @MaxValue=DataValue and @MaxTime=DataTime

   SELECT TOP 1 DataValue, DataTime FROM SensorData WHERE SensorID=@SensorID ORDER BY DataValue ASC
   -- want to set @MinValue=DataValue and @MinTime=DataTime
END
GO

Open in new window

LVL 1
JahelkaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
OriNetworksCommented:
The query you are using seems pretty accurate except you would use
SELECT @SensorDescription = description from...

You could also do multiple parameters in one select such as select @param1= field1, @param2=field2 from ....
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
JahelkaAuthor Commented:
Man, and here I was thinking this was going to be complicated.  I've been using stored procedures for a long time, but rarely need to return anything but a data set.  Can't believe how easy this was, and how little I still know about the power of sprocs!  Thanks for not calling me an idiot.  Works GREAT!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.