Avatar of garethtnash
garethtnash
Flag for United Kingdom of Great Britain and Northern Ireland 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
Microsoft SQL Server 2008Microsoft SQL Server 2005Microsoft SQL Server

Avatar of undefined
Last Comment
Lee

8/22/2022 - Mon
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

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
garethtnash

ASKER
Neat - thank you
Lee

Where did @addressid come from? That wasn't in the question.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.