Weird error, probably easy
-
For some reason this code is giving me an error around the else that i noted. When i take out the transaction stuff for the 1st part, it works fine. Any ideas?
declare @ReturnValue int
IF EXISTS (
SELECT
*
FROM
clent
WHERE
SID=''
 
----------------------------------------------------------------
--IF A RECORD IS FOUND, UPDATE IT:
----------------------------------------------------------------
BEGIN TRANSACTION
UPDATE
--tblname
ztest
SET
--col names = values
test = 'bla'
WHERE
--whatever = whatever
[id]=1
------------------------------------------------
-- CHECKS FOR ERROR ON THE UPDATE
--------------------------------------------------------
IF @@ERROR <> 0
BEGIN
SET @ReturnValue = -1
ROLLBACK
END
ELSE
SET @ReturnValue = 1
COMMIT TRANSACTION
----------------------------------------------------------------
--IF A RECORD IS NOT FOUND, INSERT NEW RECORD:
----------------------------------------------------------------
ELSE --*****IT SAYS THERE IS AN ERROR AROUND THIS ELSE******
BEGIN TRANSACTION
INSERT INTO
--table name
ztest
(
-- colnames
test
 
VALUES
(
--values
'this worked'
 
------------------------------------------
-- CHECKS FOR ERROR ON THE INSERT
-----------------------------
IF @@ERROR <> 0
BEGIN
SET @ReturnValue = -1
ROLLBACK
END
ELSE
SET @ReturnValue = @@IDENTITY
COMMIT TRANSACTION
-
I'm a little confused. Could you please be more specific? WHere should I add begin/end?
-
IF (expression)
BEGIN
BEGIN TRANSACTION
... some code ...
COMMIT TRANSACTION
END
ELSE
BEGIN
BEGIN TRANSACTION
... some code ...
COMMIT TRANSACTION
END
this help???
-
ill be at work around 1pm cst tomorrow and ill let you know
-
It's impossible to say, based on your code. There's ambiguity about where the IF Else begins and ends. Always use the form:
if condition
BEGIN
do something
END
Else
BEGIN
so something else
END
If you don't use this form only the first statement after "IF" will be executed. Using the "Begin Transaction" makes it even worse...I would use an explicit transaction name as well (IE: Begin Transaction Tran1).
Literally, your code is being executed like so:
declare @ReturnValue int
IF EXISTS (
SELECT
*
FROM
clent
WHERE
SID=''
BEGIN
----------------------------------------------------------------
--IF A RECORD IS FOUND, UPDATE IT:
--CDL: Actually, your only beginning the transaction if the record exists, nothing else
----------------------------------------------------------------
BEGIN TRANSACTION
END
---THIS WILL ALWAYS EXECUTE!!
UPDATE
--tblname
ztest
SET
--col names = values
test = 'bla'
WHERE
--whatever = whatever
[id]=1
------------------------------------------------
-- CHECKS FOR ERROR ON THE UPDATE
--------------------------------------------------------
IF @@ERROR <> 0
BEGIN
SET @ReturnValue = -1
ROLLBACK
END
ELSE
BEGIN
SET @ReturnValue = 1
END
---CDL YOUR ALWAYS EXECUTING THE COMMIT (AND ONLY BEGINING IT IF THE RECORD EXISTS
COMMIT TRANSACTION
----------------------------------------------------------------
--IF A RECORD IS NOT FOUND, INSERT NEW RECORD:
----------------------------------------------------------------
ELSE --*****IT SAYS THERE IS AN ERROR AROUND THIS ELSE******
BEGIN
BEGIN TRANSACTION
END
INSERT INTO
--table name
ztest
(
-- colnames
test
VALUES
(
--values
'this worked'
------------------------------------------
-- CHECKS FOR ERROR ON THE INSERT
-----------------------------
IF @@ERROR <> 0
BEGIN
SET @ReturnValue = -1
ROLLBACK
END
ELSE
BEGIN
SET @ReturnValue = @@IDENTITY
END
COMMIT TRANSACTION
Signature is NULL
-
Think of the BEGIN and END statements in TSQL as being like the curly braces in JavaScript, C#, C++, C, Java etc.
BEGIN = {
END = }
As with these languages TSQL assumes that the IF statement is only concerned with the next statement it comes across.
-
I'd take a different tac - why not wrap your entire quiry in one transaction instead of two?
so
begin tran
if x
do .....
commit tran
-
thanks all for the help. I needed to add the begin and end around the ifs, just like yall said. heres the working code:
declare @ReturnValue int
IF EXISTS (
SELECT
*
FROM
clent
WHERE
SID=''
)
BEGIN
----------------------------------------------------------------
--IF A RECORD IS FOUND, UPDATE IT:
----------------------------------------------------------------
BEGIN TRANSACTION
UPDATE
--tblname
ztest
SET
--col names = values
test = 'bla'
WHERE
--whatever = whatever
[id]=1
------------------------------------------------
-- CHECKS FOR ERROR ON THE UPDATE
------------------------------------------------
IF @@ERROR <> 0
BEGIN
SET @ReturnValue = -1
ROLLBACK
END
ELSE
SET @ReturnValue = 1
COMMIT TRANSACTION
END
----------------------------------------------------------------
--IF A RECORD IS NOT FOUND, INSERT NEW RECORD:
----------------------------------------------------------------
ELSE
BEGIN
BEGIN TRANSACTION
INSERT INTO
--table name
ztest
(
-- colnames
test
)
VALUES
(
--values
'this worked'
)
-------------------------------------
-- CHECKS FOR ERROR ON THE INSERT
-------------------------------------
IF @@ERROR <> 0
BEGIN
SET @ReturnValue = -1
ROLLBACK
END
ELSE
SET @ReturnValue = @@IDENTITY
COMMIT TRANSACTION
END
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply