asked on

Select A OR B SQL

Hi,

I need to run a select statement, which varies on if @var exists or not, so -

if @var does not exist;

Select TOP(1)
ID
From
dbo.[Member-Address]
Where MemberID = @memberid
order by ID desc

if @var does exist;

Select ID
From
dbo.[Member-Address]
Where MemberID = @memberid AND ID = @var

I would like to do it in one statement if possible?

Thanks

Lee

You're not going to get it in one statement as the two statements do different things.

You will end up with:

if exists (select id from dbo.[Member-Address] Where MemberID = @memberid AND ID = @var)
    select id from dbo.[Member-Address] Where MemberID = @memberid AND ID = @var
else
    select top 1 id from dbo.[Member-Address] Where MemberID = @memberid order by id desc

Open in new window

jorgedeoliveiraborges

You could use the IF statement.

eg:

DECLARE @compareprice money, @cost money 
EXECUTE Production.uspGetList '%Bikes%', 700, 
    @compareprice OUT, 
    @cost OUTPUT
IF @cost <= @compareprice 
BEGIN
    PRINT 'These products can be purchased for less than 
    $'+RTRIM(CAST(@compareprice AS varchar(20)))+'.'
END
ELSE
    PRINT 'The prices for all products in this category exceed 
    $'+ RTRIM(CAST(@compareprice AS varchar(20)))+'.'

Open in new window

jorgedeoliveiraborges

IF Boolean_expression 
     { sql_statement | statement_block } 
[ ELSE 
     { sql_statement | statement_block } ]

Open in new window

garethtnash

ASKER

I must have my IF ELSE in the wrong place -

CREATE PROCEDURE [dbo].[MemberDirectDeliveryAddress]
(
@memberid int,
@addressid int
)
AS
BEGIN
SET NOCOUNT ON;
IF @addressid <> '' then
Select TOP(1)
ID
From 
dbo.[Member-Address]
Where MemberID = @memberid
order by ID desc
ELSE
Select ID
From 
dbo.[Member-Address]
Where MemberID = @memberid AND ID = @addressid
END
GO

Open in new window

ASKER CERTIFIED SOLUTION

wdosanjos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

garethtnash

ASKER

Neat - thank you

Lee

Where did @addressid come from? That wasn't in the question.