Simplify This SQL Statement

Hi Guys!

What is the simplest way that this can be expressed? All I want to output is the Program_ID - whether freshly created or not:


SET NOCOUNT OFF
IF NOT EXISTS (
      SELECT Program_ID
      FROM A_Program
      WHERE
            (Program_Name = 'Other') AND
            (Payroll_ID = 0) AND
            (Division_ID = 1234)
      )
      BEGIN
      INSERT INTO A_Program (
            Program_Name,
            Payroll_ID,
            Division_ID
            )
      VALUES (
            'Other',
            0,
            1234
            )
      SELECT @@IDENTITY AS Program_ID
      END
ELSE
      BEGIN
      SELECT Program_ID
      FROM A_Program
      WHERE
            (Program_Name = 'Other') AND
            (Payroll_ID = 0) AND
            (Division_ID = 1234)
      END
SET NOCOUNT ON


Thanx

Dave
dbabbittAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
HilaireConnect With a Mentor Commented:
Here's an option

SET NOCOUNT OFF
     DECLARE @ID int
     SELECT @ID = Program_ID
     FROM A_Program
     WHERE
          (Program_Name = 'Other') AND
          (Payroll_ID = 0) AND
          (Division_ID = 1234)
    IF @@ROWCOUNT = 0
    BEGIN
     INSERT INTO A_Program (
          Program_Name,
          Payroll_ID,
          Division_ID
          )
     VALUES (
          'Other',
          0,
          1234
          )
     SELECT @ID = scope_identity()
     END
select @ID
SET NOCOUNT ON
0
 
MartinCMSCommented:
your statement is as simple as it can get.  However, combine your insert with select into one query like....


INSERT INTO A_Program (Program_Name,Payroll_ID,Division_ID)
SELECT Program_Name,Payroll_ID,Division_ID
FROM A_Program
WHERE (Program_Name = 'Other') AND (Payroll_ID = 0) AND (Division_ID = 1234)
0
 
FDzjubaCommented:
DECLARE @programID as uniqueidentifier;

SET @programID = (SELECT Program_ID
     FROM A_Program
     WHERE
          (Program_Name = 'Other') AND
          (Payroll_ID = 0) AND
          (Division_ID = 1234)
     )

IF @programID IS NULL
BEGIN
   SET @programID =newid();
   INSERT INTO A_Program (
          program_id,
          Program_Name,
          Payroll_ID,
          Division_ID
          )
     VALUES (
          @programID,
          'Other',
          0,
          1234
          )
      SELECT @programID AS Program_ID
END
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.

 
dbabbittAuthor Commented:
Comment from MartinCMS gets:

Server: Msg 206, Level 16, State 2, Line 3
Operand type clash: int is incompatible with uniqueidentifier
Server: Msg 206, Level 16, State 1, Line 14
Operand type clash: uniqueidentifier is incompatible with int


I might spend some time making it work, though.
0
 
dbabbittAuthor Commented:
Whoops! That was Comment from FDzjuba.
0
 
dbabbittAuthor Commented:
Comment from Hilaire gets two rowsets when the 'Other' is initially not there. Is that a bad thing? Can the SQL be cleaned up so that it doesn't appear?
0
 
FDzjubaCommented:
ok, i thought you use uniqueidentifier, try this one then

DECLARE @programID as int;

SET @programID = (SELECT Program_ID
     FROM A_Program
     WHERE
          (Program_Name = 'Other') AND
          (Payroll_ID = 0) AND
          (Division_ID = 1234)
     )

IF @programID IS NULL
BEGIN
   INSERT INTO A_Program (
          Program_Name,
          Payroll_ID,
          Division_ID
          )
     VALUES (
          'Other',
          0,
          1234
          )
      SELECT @@IDENTITY AS Program_ID
END
ELSE
BEGIN
    SELECT @programID AS Program_ID
END
0
 
dbabbittAuthor Commented:
Also, I have my SET NOCOUNT ON and SET NOCOUNT OFF mixed up. :-\
0
 
FDzjubaCommented:
what do you mean?

if you want to @@IDENTITY  to work, i think  you need SET NOCOUNT OFF.
0
 
Anthony PerkinsCommented:
FDzjuba,

>>if you want to @@IDENTITY  to work, i think  you need SET NOCOUNT OFF. <<
Really?  I think yu may want to double check this.  There is no relation between @@IDENTITY and SET NOCOUNT ON | OFF
0
 
Anthony PerkinsCommented:
>>Also, I have my SET NOCOUNT ON and SET NOCOUNT OFF mixed up.<<
You will get better performance with SET NOCOUNT ON
0
 
Anthony PerkinsCommented:
Also, can we see some movement on these abandoned questions:
1 07/20/2004 500 Best HTML Row Balancing Algorithm  Open ColdFusion
2 03/24/2004 500 ODBC--call failed Error in SQL Record Se...  Open Active Server Pages (ASP)
0
 
dbabbittAuthor Commented:
Yes, I still need answers on those questions - are you going to help?
0
 
Anthony PerkinsCommented:
Thanks.  I appreciate you closing out those old questions.
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.

All Courses

From novice to tech pro — start learning today.