Nested If Else Problem in Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Nested If Else Problem in Stored Procedure

Author

Topic

wabaker95
Starting Member

12 Posts

Posted - 2010-11-22 : 10:17:47

USE [memberWORKS_Development]
GO
/****** Object: StoredProcedure [dbo].[InsertABAInformation] Script Date: 11/22/2010 09:00:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertABAInformation]
@RT varchar(50) = null,
@TelegraphicName varchar(50),
@CustomerName varchar(50),
@OfficeCode varchar(50) = null,
@RecordTypeCode varchar(50) = null,
@ServicingNumber varchar(50) = null,
--@ACHChangeDate smalldatetime ,
@ACHNewRoutingNumber varchar(50) = null,
@State varchar(10),
@City varchar(50),
@EligibleForFundsTransfer bit = 0,
@SettlementOnly bit = 0,
@EligibleForSecuritiesTransfer bit = 0,
@LastRevision smalldatetime = null,
@Active bit = 1,
@ImportID smallint
AS

BEGIN

If @ImportID = 1
BEGIN
IF EXISTS (SELECT * FROM dbo.ABAInformation (NOLOCK) WHERE dbo.ABAInformation.rt = @RT)
-- BEGIN
UPDATE dbo.ABAInformation
SET TelegraphicName = @TelegraphicName,
CustomerName = @CustomerName,
OfficeCode = @OfficeCode,
RecordTypeCode = @RecordTypeCode,
ServicingNumber = @ServicingNumber,
--ACHChangeDate = @ACHChangeDate,
ACHNewRoutingNumber = @ACHNewRoutingNumber,
State = @State,
City = @City,
EligibleForFundsTransfer = @EligibleForFundsTransfer,
SettlementOnly = @SettlementOnly,
EligibleForSecuritiesTransfer = @EligibleForSecuritiesTransfer,
LastRevision = @LastRevision,
ModifyDate = GETUTCDATE(),
Active = @Active
WHERE dbo.ABAInformation.rt = @RT
END
ELSE
BEGIN
INSERT dbo.ABAInformation (
RT,
TelegraphicName,
CustomerName,
OfficeCode,
RecordTypeCode,
ServicingNumber,
ACHChangeDate,
ACHNewRoutingNumber,
State,
City,
EligibleForFundsTransfer,
SettlementOnly,
EligibleForSecuritiesTransfer,
LastRevision,
Active,
AddDate,
ModifyDate
) VALUES (
@RT,
@TelegraphicName,
@CustomerName,
@OfficeCode,
@RecordTypeCode,
@ServicingNumber,
--@ACHChangeDate,
@ACHNewRoutingNumber,
@State,
@City,
@EligibleForFundsTransfer,
@SettlementOnly,
@EligibleForSecuritiesTransfer,
@LastRevision,
@Active,
GETUTCDATE(),
-- GETUTCDATE() )
End
End
Else
BEGIN

IF EXISTS (SELECT * FROM dbo.ABAInformation (NOLOCK) WHERE dbo.ABAInformation.rt = @RT)
BEGIN
UPDATE dbo.ABAInformation
SET TelegraphicName = @TelegraphicName,
CustomerName = @CustomerName,
OfficeCode = @OfficeCode,
RecordTypeCode = @RecordTypeCode,
ServicingNumber = @ServicingNumber,
--ACHChangeDate = @ACHChangeDate,
ACHNewRoutingNumber = @ACHNewRoutingNumber,
State = @State,
City = @City,
EligibleForFundsTransfer = @EligibleForFundsTransfer,
SettlementOnly = @SettlementOnly,
EligibleForSecuritiesTransfer = @EligibleForSecuritiesTransfer,
LastRevision = @LastRevision,
ModifyDate = GETUTCDATE(),
Active = @Active
WHERE dbo.ABAInformation.rt = @RT
END
ELSE
BEGIN
INSERT dbo.ABAInformation (
RT,
TelegraphicName,
CustomerName,
OfficeCode,
--RecordTypeCode,
--ServicingNumber,
--ACHChangeDate,
--ACHNewRoutingNumber,
State,
City,
EligibleForFundsTransfer,
SettlementOnly,
EligibleForSecuritiesTransfer,
LastRevision,
Active,
AddDate,
ModifyDate
) VALUES (
@RT,
' ',
@CustomerName,
@OfficeCode,
@RecordTypeCode,
@ServicingNumber,
--@ACHChangeDate,
@ACHNewRoutingNumber,
@State,
@City,
0,
0,
0,
@LastRevision,
@Active,
GETUTCDATE(),
GETUTCDATE() )

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2010-11-22 : 10:24:06

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

-----------

I used to think I wasn't a morning person but it never got better in the afternoon

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-11-22 : 11:57:52

There is so much wrong I'm not even sure what to say. I'd suggest that you look in Books Online for how IF..ELSE works as well and BEGIN..END Pairs.

I took the liberty if reformatting your code, ommiting the actual "code" so you can begin to see the issue:

BEGIN
    If @ImportID = 1 
        BEGIN
        IF EXISTS (SELECT * FROM dbo.ABAInformation (NOLOCK) WHERE dbo.ABAInformation.rt = @RT)
        -- BEGIN
        -- <snip>
        END
    ELSE
        BEGIN
    -- <snip>
        End 
End
Else -- What is that?
BEGIN
    IF EXISTS (SELECT * FROM dbo.ABAInformation (NOLOCK) WHERE dbo.ABAInformation.rt = @RT)
    BEGIN
    -- <snip
    END
    ELSE
    BEGIN
    -- <snip

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-22 : 12:41:19

but you didn't mention the problem

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

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

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

wabaker95
Starting Member

12 Posts

Posted - 2010-11-29 : 09:21:09

Thank you Lamprey. By you providing me with the formatting I was able to get the issue solved. Also next time I will adhere to the forum rules and ask a question the correct way.

quote:
Originally posted by Lamprey

There is so much wrong I'm not even sure what to say. I'd suggest that you look in Books Online for how IF..ELSE works as well and BEGIN..END Pairs.

I took the liberty if reformatting your code, ommiting the actual "code" so you can begin to see the issue:
BEGIN
If @ImportID = 1
BEGIN
IF EXISTS (SELECT * FROM dbo.ABAInformation (NOLOCK) WHERE dbo.ABAInformation.rt = @RT)
-- BEGIN
-- <snip>
END
ELSE
BEGIN
-- <snip>
End
End
Else -- What is that?
BEGIN
IF EXISTS (SELECT * FROM dbo.ABAInformation (NOLOCK) WHERE dbo.ABAInformation.rt = @RT)
BEGIN
-- <snip
END
ELSE
BEGIN
-- <snip

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources