asked on

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

MartinCMS

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)

FDzjuba

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

ASKER CERTIFIED SOLUTION

Hilaire

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dbabbitt

ASKER

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.

dbabbitt

ASKER

Whoops! That was Comment from FDzjuba.

dbabbitt

ASKER

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?

FDzjuba

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

dbabbitt

ASKER

Also, I have my SET NOCOUNT ON and SET NOCOUNT OFF mixed up. :-\

FDzjuba

what do you mean?

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

Anthony Perkins

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

Anthony Perkins

>>Also, I have my SET NOCOUNT ON and SET NOCOUNT OFF mixed up.<<
You will get better performance with SET NOCOUNT ON

Anthony Perkins

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)

dbabbitt

ASKER

Yes, I still need answers on those questions - are you going to help?

Anthony Perkins

Thanks. I appreciate you closing out those old questions.