Solved

SQL - Select using If then else and break - newbie!

Posted on 2010-11-25
15
782 Views
Last Modified: 2012-08-13
Hi I'm a SQL Newbie so I have put something down (which I know is wrong but I'm hoping this will help you understand what I'm wanting to do:

I have 3 variables:
@postcode1 varchar(8),
@postcode2 varchar(8),
@postcode3 varchar(8),

I want to get the record(s) from the tProdPostcode table whenever the postcode exists in the table.   I only want one set of results so I need to break out whenever records are returned.
Is it possible to do something like this:
BEGIN
   IF (SELECT * FROM tProdPostcode
      WHERE Postcode = postcode1)
      BREAK
   ELSE IF (SELECT * FROM tProdPostcode
      WHERE Postcode = postcode2)
      BREAK
   ELSE IF (SELECT * FROM tProdPostcode
      WHERE Postcode = postcode3)
      BREAK
   ELSE
      BREAK
END
Please advise what the correct syntax is to achieve the correct result set.

Thanks
Lorna
0
Comment
Question by:Lorna70
  • 5
  • 3
  • 3
  • +3
15 Comments
 
LVL 24

Expert Comment

by:jimyX
ID: 34213954
Break is used when you want to break a loop but if-statement does not require a break as the if-block code is executed if the condition is true other wise else block is executed.
0
 
LVL 24

Expert Comment

by:jimyX
ID: 34213964
If-Condition should result in True or False: what do you expect from:
IF (SELECT * FROM tProdPostcode WHERE Postcode = postcode1)


What did you assign to these variables for matching against:

postcode1
postcode2
postcode3
0
 

Author Comment

by:Lorna70
ID: 34213973
OK but haven't worked with an if statement before :-).  Would the following return records or is it just checking that a record exists??

IF (SELECT * FROM tProdPostcode
      WHERE Postcode = postcode1)
0
 
LVL 24

Expert Comment

by:jimyX
ID: 34213987
This will return all the records that match the condition specified in where clause (Postcode = postcode1).
0
 
LVL 1

Expert Comment

by:sisiliano
ID: 34213989
You may check this link for some code to use BREAK.

http://msdn.microsoft.com/en-us/library/ms178642.aspx

And i believe i would be better to use a select case instead of an if then else.

0
 
LVL 24

Assisted Solution

by:jimyX
jimyX earned 83 total points
ID: 34213996
To give a simpler example about the if-statement and the condition:
DECLARE
    a NUMBER;
    b NUMBER;
BEGIN
    SELECT e,f INTO a,b FROM T1 WHERE e>1;
    IF b=1 THEN
        INSERT INTO T1 VALUES(b,a);
    ELSE
        INSERT INTO T1 VALUES(b+10,a+10);
    END IF;
END;
0
 
LVL 24

Assisted Solution

by:jimyX
jimyX earned 83 total points
ID: 34214032
The variable a is used to store the value of column e and the variable b is used to store the value of column f and the selected records will be based on the value of column e that is greater than 1.

The IF is used to match the stored value in b whether it is equal to 1 then insert into the table the values of b and a. If the value of b was not equal to 1 then insert the values b and a after adding 10 to each of them.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 51

Expert Comment

by:HainKurt
ID: 34214105
all in one
declare 

 @s1 int;

 @s2 int;

 @s3 int;

begin

	SELECT 

	sum(case when postcode=@postcode1 then 1 else 0 end),

	sum(case when postcode=@postcode2 then 1 else 0 end),

	sum(case when postcode=@postcode3 then 1 else 0 end)

	into @s1,@s2,@s3

	FROM tProdPostcode

	WHERE Postcode in (@postcode1, @postcode2, @postcode3);



	if (@s1>0)...

	if (@s2>0)...

	if (@s3>0)...

END;

Open in new window

0
 
LVL 32

Accepted Solution

by:
ewangoya earned 42 total points
ID: 34214155

Here's the way to do it

declare @postcode1 varchar(8)
declare @postcode2 varchar(8)
declare @postcode3 varchar(8)

declare @RowsAffected integer

SELECT * FROM tProdPostcode WHERE Postcode = @postcode1
SET @RowsAffected = @@RowCount
IF @RowsAffected <= 0
BEGIN
  SELECT * FROM tProdPostcode WHERE Postcode = @postcode2
  SET @RowsAffected = @@RowCount
  IF @RowsAffected <= 0
  BEGIN
    SELECT * FROM tProdPostcode WHERE Postcode = @postcode3
  END
END
0
 
LVL 1

Expert Comment

by:sisiliano
ID: 34215182
You could do it this way:

DECLARE @POSTCODE1 VARCHAR(8)
DECLARE @POSTCODE2 VARCHAR(8)
DECLARE @POSTCODE3 VARCHAR(8)

SELECT * FROM tProdPostcode
WHERE Postcode = @Postcode1
OR Postcode = @Postcode2
OR Postcode = @Postcode3

Or do it this way too, it will all depend what you want exactly.

DECLARE @POSTCODE1 VARCHAR(8)
DECLARE @POSTCODE2 VARCHAR(8)
DECLARE @POSTCODE3 VARCHAR(8)

SELECT * FROM tProdPostcod
WHERE POSTCODE IN (@POSTCODE1, @POSTCODE2,@POSTCODE3)

Check the link I mentioned before or check this one:

http://msdn.microsoft.com/en-us/library/ms188047.aspx

You could even find out that you can do something diferent that could even give you better results.

0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 84 total points
ID: 34215895
here is the suggestion, similar that above, still different ...

select * from (
SELECT *, row_number() over (order by case when postcode = @postcode1 then 1 when postcode = @postcode2 then 2 when postcode = @postcode3 then 3 else 99 end) rn
   FROM tProdPostcode
      WHERE Postcode IN( @postcode1, @postcode2, @postcode3 )
 ) sq
where sq.rn = 1

Open in new window

0
 

Author Comment

by:Lorna70
ID: 34224866
Thanks - I will try out some of these and then allocate points.  Although I'm not sure what is the best approach so I'lI explain it a bit better this time:

I have a table of UK postcodes but not all postcodes are listed and some are only the first character, first 2 chars, first 3 chars and first 4 chars e.g for Glasgow postcodes there may only be a G for the postcode but for edin there may be EH and for Inverness there may be IV12.  Therefore, I have to search for the record matching the first 4 chars of my postcode first and if that returns no records, I search for the first 3 chars, then the first 2, then the first char.  So basically I need to stop searching when a record is returned.
Any advice on the best approach would be very welcome :-)
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 84 total points
ID: 34225011
so, not a IN, but some LIKE ....

so, presuming @postcode1 is the one with 4 digits (+ the trailing %), @postcode2 the one with 3 digits etc...
select * from (

SELECT *, row_number() over (order by case when postcode LIKE @postcode1 then 1 when postcode LIKE @postcode2 then 2 when postcode LIKE @postcode3 then 3 else 99 end) rn

   FROM tProdPostcode

      WHERE Postcode LIKE @postcode1

         OR Postcode LIKE @postcode2

         OR Postcode LIKE @postcode3 

 ) sq

where sq.rn = 1

Open in new window

0
 
LVL 1

Assisted Solution

by:sisiliano
sisiliano earned 41 total points
ID: 34226480
As mentioned in my first posted comment i believe you should use a select case and as you wanted you may break in case you find any result set.

Look a the links there are many code samples that may help you.

Let me assume that you are posting a value to search for from an application, you assign it to a variable @postcode, if that's the case you not even need more variables you just need to search for a value that's like the one your looking for no matter how many chars the value you are searching has (1,2,3,4) you just assign this one char value to the @postcode variable and do a select using LIKE which will return the values that match the chars contained in the @postcode no matter if postcode has ('G','EH').

You just need to assign to the variable the values to look for.


SELECT * FROM tPostcode
WHERE Postcode
LIKE @postcode

Unless you're looking for all the values (1,2,3 and 4 chars)

Best Regards Sisiliano!!
0
 

Author Closing Comment

by:Lorna70
ID: 34226577
Thanks everyone - don't know what I'd do without you guys :-)
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now