Using SELECT within function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using SELECT within function

Author

Topic

dougiel
Starting Member

4 Posts

Posted - 2008-07-03 : 05:36:22

I'd like a function to return a field from the most recent record in a set. I want to write:

DECLARE @LatestValue nchar(10)
DECLARE @ReturnValue nchar(10)

SET @Latestvalue = SELECT TOP(1) FieldofInterest
FROM tablename
WHERE somecondition
ORDER BY date DESC

SET @ReturnValue = @LatestValue

But that throws an error. What will work?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-03 : 05:40:28

you can directly return that variabale. no need of second variable


DECLARE @LatestValue nchar(10)

SET @Latestvalue = SELECT TOP(1) FieldofInterest 
FROM tablename 
WHERE somecondition
ORDER BY date DESC

RETURN  @Latestvalue

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-07-03 : 05:41:31

[code]create function dbo.foooo()
returns nchar(10)
as
begin
declare @Latestvalue nchar(10)

select top 1 @Latestvalue = FieldofInterest
FROM tablename
WHERE somecondition
ORDER BY date DESC

return(@Latestvalue)
end[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

dougiel
Starting Member

4 Posts

Posted - 2008-07-03 : 06:25:22

Thank you Harsh - that is the syntax that works.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-03 : 07:34:50

you could have used ::

SET @Latestvalue = (
SELECT TOP(1) FieldofInterest
FROM tablename
WHERE somecondition
ORDER BY date DESC
)

but SELECT TOP(1) @latestValue = .......

is easier.

-------------
Charlie

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-03 : 10:36:51

quote:
Originally posted by visakh16

you can directly return that variabale. no need of second variable

DECLARE @LatestValue nchar(10)

SET @Latestvalue = (SELECT TOP(1) FieldofInterest
FROM tablename
WHERE somecondition
ORDER BY date DESC)

RETURN @Latestvalue

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-03 : 11:31:28

Beware of "lookup" functions like this; they are just about always much less efficient than writing JOINS.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources