IF logic on SELECT statement - 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)

IF logic on SELECT statement

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2009-09-29 : 09:56:53

I'm hoping to have an SP that can follow the logic below, but no idea of the correct syntax, can anyone help

CREATE PROCEDURE sp_Test

@vehicleref, int

IF @vehicleref IN (SELECT vehicleRef FROM table 1 WHERE source = 'A' AND [type] = 'car') THEN

SELECT TOP 1 term,milespa,maintained,ch,pch FROM tblNewMatrixFigures WHERE ch > 0 AND vehicleref = {0} AND term = 3 ORDER BY ch ASC

ELSEIF @vehicleref IN (SELECT vehicleRef FROM table 1 WHERE source = 'A' AND [type] = 'van') THEN

SELECT TOP 1 term,milespa,maintained,ch,pch FROM tblNewMatrixFigures WHERE ch > 0 AND vehicleref = {0} AND term = 4 ORDER BY ch ASC

ELSE

SELECT TOP 1 term,milespa,maintained,ch,pch FROM tblNewMatrixFigures WHERE ch > 0 AND vehicleref = {0} ORDER BY ch ASC

Thanks

rocknpop
Posting Yak Master

201 Posts

Posted - 2009-09-29 : 10:19:00

CREATE PROCEDURE sp_Test
@vehicleref int
AS

IF @vehicleref IN (SELECT vehicleRef FROM table1 WHERE source = 'A' AND [type] = 'car')
SELECT TOP 1 term,milespa,maintained,ch,pch FROM tblNewMatrixFigures WHERE ch > 0 AND vehicleref = @vehicleref AND term = 3
ELSE IF @vehicleref IN (SELECT vehicleRef FROM table1 WHERE source = 'A' AND [type] = 'van')
SELECT TOP 1 term,milespa,maintained,ch,pch FROM tblNewMatrixFigures WHERE ch > 0 AND vehicleref = @vehicleref AND term = 4
ELSE
SELECT TOP 1 term,milespa,maintained,ch,pch FROM tblNewMatrixFigures WHERE ch > 0 AND vehicleref = @vehicleref

--------------------
Rock n Roll with SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 13:10:22

you dont need IF ELSE at all. do something like


CREATE PROCEDURE sp_Test

@vehicleref, int

SELECT TOP 1 term,milespa,maintained,ch,pch 
FROM tblNewMatrixFigures t
JOIN table 1 t1
ON t1.vehicleRef=t.vehicleref
WHERE t.ch > 0 
AND t.vehicleref = {0} 
AND t.term = CASE WHEN t1.source = 'A' AND t1.[type] = 'car' THEN 3 
                  WHEN t1.source = 'A' AND t1.[type] = 'van' THEN 4
                  ELSE t.term
             END
ORDER BY t.ch ASC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources