Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 801
  • Last Modified:

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

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
Lorna70
Asked:
Lorna70
  • 5
  • 3
  • 3
  • +3
6 Solutions
 
jimyXCommented:
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
 
jimyXCommented:
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
 
Lorna70Author Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
jimyXCommented:
This will return all the records that match the condition specified in where clause (Postcode = postcode1).
0
 
sisilianoCommented:
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
 
jimyXCommented:
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
 
jimyXCommented:
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
 
HainKurtSr. System AnalystCommented:
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
 
Ephraim WangoyaCommented:

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
 
sisilianoCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Lorna70Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
sisilianoCommented:
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
 
Lorna70Author Commented:
Thanks everyone - don't know what I'd do without you guys :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 5
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now