Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Simplify This SQL Statement

Posted on 2004-09-20
14
Medium Priority
?
226 Views
Last Modified: 2011-08-18
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
0
Comment
Question by:dbabbitt
  • 5
  • 4
  • 3
  • +2
14 Comments
 
LVL 8

Expert Comment

by:MartinCMS
ID: 12103584
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
 
LVL 7

Expert Comment

by:FDzjuba
ID: 12103587
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
 
LVL 26

Accepted Solution

by:
Hilaire earned 150 total points
ID: 12103634
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:dbabbitt
ID: 12103674
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
 

Author Comment

by:dbabbitt
ID: 12103689
Whoops! That was Comment from FDzjuba.
0
 

Author Comment

by:dbabbitt
ID: 12103737
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
 
LVL 7

Expert Comment

by:FDzjuba
ID: 12103894
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
 

Author Comment

by:dbabbitt
ID: 12104212
Also, I have my SET NOCOUNT ON and SET NOCOUNT OFF mixed up. :-\
0
 
LVL 7

Expert Comment

by:FDzjuba
ID: 12104275
what do you mean?

if you want to @@IDENTITY  to work, i think  you need SET NOCOUNT OFF.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12108879
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12108881
>>Also, I have my SET NOCOUNT ON and SET NOCOUNT OFF mixed up.<<
You will get better performance with SET NOCOUNT ON
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12108890
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
 

Author Comment

by:dbabbitt
ID: 12110911
Yes, I still need answers on those questions - are you going to help?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12112304
Thanks.  I appreciate you closing out those old questions.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

885 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