Hi - I am working on the query below and am trying to incorporate the
IF ELSE logic to allow for an update if a record exists already in the
tranattendance table and otherwise an insert if it does not. I am new
to using IF ELSE. I think I am close but am getting errors related to
BEGIN and ELSE. Can someone help?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_dailyattendance_test]

AS

SET NOCOUNT ON;

--declaring constant variables required by table
declare @people_code varchar (1)
declare @create_date datetime
declare @create_time datetime
declare @create_opid varchar(8)
declare @create_terminal varchar(4)
declare @revision_date datetime
declare @revision_time datetime
declare @revision_opid varchar(8)
declare @revision_terminal varchar(4)
declare @abt_join varchar(1)

--setting input values for constant variables
set @people_code = 'P'
set @create_opid = 'blah'
set @create_terminal = '0001'
set @revision_opid = 'blah'
set @revision_terminal = '0001'
set @abt_join = '*'

--datetime constants will all be set to current date
select @create_date = getdate()
select @create_time = getdate()
select @revision_date = getdate()
select @revision_time = getdate()

--update statement if attendance record already exists
IF (SELECT *
FROM training.dbo.tranattendance AS T,
view_mdl_attendance as att
WHERE T.people_code_id = att.people_code_id
AND T.academic_year = att.academic_year
AND T.academic_term = att.academic_term
AND T.academic_session = att.session
AND T.event_id = att.event_id
AND T.section = att.section
AND T.attendance_date = att.attendance_date)
BEGIN
UPDATE training.dbo.tranattendance
SET attendance_status = att.attendance_status,
attendance_date = att.attendance_date,
revision_opid = 'blah'
FROM training.dbo.sections as s
INNER JOIN view_mdl_attendance as att
ON s.academic_year = att.academic_year AND
s.academic_term = att.academic_term AND
s.academic_session = att.session AND
s.event_id = att.event_id and
s.section = att.section
END

--insert statement if no attendance record exists
ELSE
BEGIN
INSERT INTO training.dbo.tranattendance
(people_code,
people_id,
people_code_id,
academic_year,
academic_term,
academic_session,
event_id,
event_sub_type,
section,
attendance_date,
attendance_status,
create_date,
create_time,
create_opid,
create_terminal,
revision_date,
revision_time,
revision_opid,
revision_terminal,
abt_join)
SELECT distinct @people_code,
att.people_id,
att.people_code_id,
att.academic_year,
att.academic_term,
att.session,
att.event_id,
s.event_sub_type,
att.section,
att.attendance_date,
att.attendance_status,
@create_date,
@create_time,
@create_opid,
@create_terminal,
@revision_date,
@revision_time,
@revision_opid,
@revision_terminal,
@abt_join
FROM training.dbo.sections as s
INNER JOIN view_mdl_attendance as att
ON s.academic_year = att.academic_year AND
s.academic_term = att.academic_term AND
s.academic_session = att.session AND
s.event_id = att.event_id and
s.section = att.section and
NOT EXISTS(SELECT *
FROM training.dbo.tranattendance AS T
WHERE T.people_code_id = att.people_code_id
AND T.academic_year = att.academic_year
AND T.academic_term = att.academic_term
AND T.academic_session = att.session
AND T.event_id = att.event_id
AND T.section = att.section
AND T.attendance_date = att.attendance_date)
END

GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

Are you using SQL 2008? If so, consider using MERGE:

http://msdn.microsoft.com/en-us/library/bb510625.aspx

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Janet" <ckauvar (AT) gmail (DOT) com> wrote

Hi - I am working on the query below and am trying to incorporate the
IF ELSE logic to allow for an update if a record exists already in the
tranattendance table and otherwise an insert if it does not. I am new
to using IF ELSE. I think I am close but am getting errors related to
BEGIN and ELSE. Can someone help?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_dailyattendance_test]

AS

SET NOCOUNT ON;

--declaring constant variables required by table
declare @people_code varchar (1)
declare @create_date datetime
declare @create_time datetime
declare @create_opid varchar(8)
declare @create_terminal varchar(4)
declare @revision_date datetime
declare @revision_time datetime
declare @revision_opid varchar(8)
declare @revision_terminal varchar(4)
declare @abt_join varchar(1)

--setting input values for constant variables
set @people_code = 'P'
set @create_opid = 'blah'
set @create_terminal = '0001'
set @revision_opid = 'blah'
set @revision_terminal = '0001'
set @abt_join = '*'

--datetime constants will all be set to current date
select @create_date = getdate()
select @create_time = getdate()
select @revision_date = getdate()
select @revision_time = getdate()

--update statement if attendance record already exists
IF (SELECT *
FROM training.dbo.tranattendance AS T,
view_mdl_attendance as att
WHERE T.people_code_id = att.people_code_id
AND T.academic_year = att.academic_year
AND T.academic_term = att.academic_term
AND T.academic_session = att.session
AND T.event_id = att.event_id
AND T.section = att.section
AND T.attendance_date = att.attendance_date)
BEGIN
UPDATE training.dbo.tranattendance
SET attendance_status = att.attendance_status,
attendance_date = att.attendance_date,
revision_opid = 'blah'
FROM training.dbo.sections as s
INNER JOIN view_mdl_attendance as att
ON s.academic_year = att.academic_year AND
s.academic_term = att.academic_term AND
s.academic_session = att.session AND
s.event_id = att.event_id and
s.section = att.section
END

--insert statement if no attendance record exists
ELSE
BEGIN
INSERT INTO training.dbo.tranattendance
(people_code,
people_id,
people_code_id,
academic_year,
academic_term,
academic_session,
event_id,
event_sub_type,
section,
attendance_date,
attendance_status,
create_date,
create_time,
create_opid,
create_terminal,
revision_date,
revision_time,
revision_opid,
revision_terminal,
abt_join)
SELECT distinct @people_code,
att.people_id,
att.people_code_id,
att.academic_year,
att.academic_term,
att.session,
att.event_id,
s.event_sub_type,
att.section,
att.attendance_date,
att.attendance_status,
@create_date,
@create_time,
@create_opid,
@create_terminal,
@revision_date,
@revision_time,
@revision_opid,
@revision_terminal,
@abt_join
FROM training.dbo.sections as s
INNER JOIN view_mdl_attendance as att
ON s.academic_year = att.academic_year AND
s.academic_term = att.academic_term AND
s.academic_session = att.session AND
s.event_id = att.event_id and
s.section = att.section and
NOT EXISTS(SELECT *
FROM training.dbo.tranattendance AS T
WHERE T.people_code_id = att.people_code_id
AND T.academic_year = att.academic_year
AND T.academic_term = att.academic_term
AND T.academic_session = att.session
AND T.event_id = att.event_id
AND T.section = att.section
AND T.attendance_date = att.attendance_date)
END

GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

"Janet" <ckauvar (AT) gmail (DOT) com> wrote

Janet, take a look at the @@ROWCOUNT system value.

UPDATE sometable
SET values

IF @@ROWCOUNT = 0
BEGIN
-- Do the Insert instead
END


Like other system values (@@ERROR for example), the value of @@ROWCOUNT is
only good for the last statement.


HTH

Rick Sawtell

Actually using SQL 2005. Sorry I should have included that. Any
suggestions on using IF and ELSE? I know it works, just can't get the
syntax quite right.

Rick's solution should do it or you.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Janet" <ckauvar (AT) gmail (DOT) com> wrote

Actually using SQL 2005. Sorry I should have included that. Any
suggestions on using IF and ELSE? I know it works, just can't get the
syntax quite right.

Janet (ckauvar (AT) gmail (DOT) com) writes:
Almost correct. Make that

IF EXISTS (SELECT *

Rick's solution of using @@rowcount has the advantage of saving you
from repeating the conditions in the UPDATE statement in IF EXISTS,
so that is a better solution.

I would like to change his suggestion one point, though:

UPDATE
....
SELECT @rowc = @@rowcount

IF @rowc = 0
BEGIN
INSERT


That is, capture @@rowcount into a local variable. As Rick points out,
@@rowcount is set after each statement. If you have:


UPDATE
...

IF @@rowcount = 0


Someone may later modify the code and insert something between the
UPDATE statement and the @@rowcount check whereupon disaster strikes.
That's why the capture of @@rowcount should follow directly on the
UPDATE statement withou any blank line in between.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

But just to play devil's advocate, someone could just as easily insert
something between the UPDATE statement and the SET/SELECT line. If you
think the absence of a blank line will be enough to present disaster, you
need to review more code changes. :-)

Aaron Bertrand (moc.liamg (AT) dnartreb (DOT) noraa) writes:
Certainly true that just because there is no blank line there is no absolute
protection. Still I think the odds are a little better.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote


Good point. In my old code (pre TRY CATCH), I used to do that for most
queries, I would also capture the error as well.

SELECT @rowx = @@ROWCOUNT, @err = @@ERROR

Rick Sawtell