Sql Server If condition whit select

Question

What's wrong with this?

  DECLARE @error int

  If (SELECT ID_Projet FROM tblProjet WHERE No_Projet=@no_Projet)> 0
     SET @error=1
  END IF

What is it supposed to do? Are you trying to test if there are >0 matching rows or test whether the ID_Projet value is >0? — Martin Smith
– Martin Smith, Commented Feb 10, 2012 at 16:45
are you trying to check number of rows returned or ID_Project is a field in the database? — Asdfg
– Asdfg, Commented Feb 10, 2012 at 16:46
Yes this is what I try Martin. Do you have a better solution? — FrankSharp
– FrankSharp, Commented Feb 10, 2012 at 17:02

aF. · Accepted Answer · 2012-02-10 16:46:52Z

6

The END IF is incorrect.

Do it like this:

 DECLARE @error int

  If (SELECT ID_Projet FROM tblProjet WHERE No_Projet=@no_Projet)> 0
     SET @error=1

or this:

 DECLARE @error int

  If (SELECT ID_Projet FROM tblProjet WHERE No_Projet=@no_Projet)> 0
  Begin
     SET @error=1
  End

Check HERE for documentation.

If you're trying to see the number of rows with that restriction you should do it like this:

 DECLARE @error int

  If (SELECT count(ID_Projet) FROM tblProjet WHERE No_Projet=@no_Projet)> 0
  Begin
     SET @error=1
  End

answered Feb 10, 2012 at 16:46

aF.

67k45 gold badges141 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

GarethD Over a year ago

+1 for correct answer and if I could another +1 for being faster at typing than me!

GarethD Over a year ago

If you are only checking if the condition is met then it should use IF EXISTS as then the table scan only goes to the first match rather than a full table scan to count the records.

GarethD · Accepted Answer · 2012-02-10 17:14:58Z

3

END IF is not T-SQL syntax for conditional statements.

DECLARE @error int

IF (SELECT COUNT(ID_Projet) FROM tblProjet WHERE No_Projet=@no_Projet)> 0
  BEGIN
    SET @error=1
  END

EDIT:

Since this is only checking for at least one row you should use the EXISTS functionality rather than COUNT. This is much more efficient, if ID_Project is nullable you will need to add a further WHERE clause to the EXISTS Query to add "AND ID_Projet IS NOT NULL" since COUNT(NULL) = 0

DECLARE @error int

IF EXISTS(SELECT ID_Projet FROM tblProjet WHERE No_Projet=@no_Projet)
  BEGIN
    SET @error=1
  END

edited Feb 10, 2012 at 17:14

answered Feb 10, 2012 at 16:47

GarethD

70k11 gold badges90 silver badges130 bronze badges

Collectives™ on Stack Overflow

Sql Server If condition whit select

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related