Multiple Selects in 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

SQL Server 2005 Forums

Transact-SQL (2005)

Multiple Selects in Function

Author

Topic

stuaz
Starting Member

16 Posts

Posted - 2011-11-17 : 16:41:19

Hi,

In a function how would I go about saying (in english)

IF X field in a table = 2 then do the following Select query, and return the result (which is an int)
ELSE
do this select statement.

What I have is that I am looking into another table to bring back the latest figure, but depending on if this certain field is a 2 or a 1 I want to do a different select statement.

Hope this makes sense...

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-11-17 : 18:01:22

Not a lot to go on here, but you probably need something like this
IF EXISTS(select * from sometable where somefield = 2)
<select something>
ELSE
<select something else>

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 03:24:16

you need to determine on what basis you want to identify latest and put that logic based on order by in your select before you do the IF condition check

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

stuaz
Starting Member

16 Posts

Posted - 2011-11-18 : 05:57:05

Hi, thanks for your reply.

This is what I have so far, but it complains about the syntax after the IF.


ALTER FUNCTION [dbo].[LASTSERVM1](@SERIAL_NUM nvarchar (15))
RETURNS int
AS
BEGIN
RETURN
(
IF EXISTS (
SELECT MACHINES.ID
FROM MACHINES
INNER JOIN MODELS ON MACHINES.MODEL = MODELS.ID
WHERE MODELS.SMETERS = '2')
	SELECT TOP 2 VISITS_RDGS.RDG
	FROM VISITS_RDGS
	INNER JOIN VISITS ON VISITS_RDGS.ID = VISITS.ID
	INNER JOIN MACHINES ON VISITS.SERIAL = MACHINES.ID
	INNER JOIN MODELS ON MACHINES.MODEL = MODELS.ID
	WHERE VISITS.SERIAL = @SERIAL_NUM
	ORDER BY VISITS_RDGS.RDATE DESC
ELSE
	SELECT TOP 1 VISITS_RDGS.RDG
	FROM VISITS_RDGS
	INNER JOIN VISITS ON VISITS_RDGS.ID = VISITS.ID
	INNER JOIN MACHINES ON VISITS.SERIAL = MACHINES.ID
	INNER JOIN MODELS ON MACHINES.MODEL = MODELS.ID
	WHERE VISITS.SERIAL = @SERIAL_NUM
	ORDER BY VISITS_RDGS.RDATE DESC	
)
END

stuaz
Starting Member

16 Posts

Posted - 2011-11-18 : 07:10:33

Actually I think I am going about this the wrong way and have split it into two functions.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources