A RETURN statement with a return value cannot be used in this context.
-
I have the following code I'm testing that will eventually become a stored procedure. I'm running it in QA and I get the error message "A RETURN statement with a return value cannot be used in this context." when I execute the query. I'd swear I've used RETURN in SP's before. Any ideas?
DECLARE @cat int
DECLARE @mod int
DECLARE @sub int
DECLARE @ver int
DECLARE @desc varchar(255)
DECLARE @na int
DECLARE @ra int
DECLARE @pa int
DECLARE @filename varchar(255)
SET @cat=3
Set @mod=5
set @sub=3
set @ver=1
set @desc='New Module'
set @na=1
set @ra=1
set @pa=1
set @filename='New_Filename'
DECLARE @rc int
DECLARE @prevdesc varchar(255)
DECLARE @err int
DECLARE @rows int
BEGIN TRANSACTION
SELECT @prevdesc FROM Submodules WHERE Category=@cat AND [Module]=@mod AND Submodule=@sub AND Version>=@ver
IF upper(@prevdesc)=upper(@desc) BEGIN
-- Insert new Version of same Submodule
print 'new Version'
INSERT INTO #temp
SELECT * FROM Submodules
WHERE Category=@cat AND [Module]=@mod AND Submodule=@sub AND Version>@ver
SET @err=@@error
SET @rows=@@rowcount
IF @err<>0 BEGIN
ROLLBACK TRANSACTION
RETURN 3
END
select * from #temp
IF @rows > 0 BEGIN
DELETE FROM Submodules
WHERE Category=@cat AND [Module]=@mod AND Submodule=@sub AND Version>@ver
set @rc=@@error
Print 'Deleted: '+cast(@@rowcount as varchar(5))
IF @@error<>0 BEGIN
ROLLBACK TRANSACTION
RETURN 4
END
UPDATE #temp SET Version=Version+1
select * from #temp
IF @@error<>0 BEGIN
ROLLBACK TRANSACTION
RETURN 5
END
INSERT INTO Submodules
SELECT * FROM #temp
IF @@error<>0 BEGIN
ROLLBACK TRANSACTION
RETURN 6
END
END
DROP TABLE #temp
INSERT INTO Submodules (Category, [Module], Submodule, Version, [Description], NA, RA, PA, [Filename])
Values(@cat, @mod, @sub, @ver+1, @desc, @na, @ra, @pa, @filename)
IF @@error<>0 @@rowcount<>1 BEGIN
ROLLBACK TRANSACTION
IF @@rowcount<>1 Return 2
IF @@error<>0 Return 1
END
select * from submodules where Category=@cat AND [Module]=@mod AND Submodule=@sub AND Version=@ver
END
ELSE BEGIN
-- Insert new Submodule
print 'New Module'
INSERT INTO #temp
SELECT * FROM Submodules
WHERE Category=@cat AND [Module]=@mod AND Submodule>@sub
SET @err=@@error
SET @rows=@@rowcount
IF @err<>0 BEGIN
ROLLBACK TRANSACTION
RETURN 7
END
select * from #temp
IF @rows > 0 BEGIN
DELETE FROM Submodules
WHERE Category=@cat AND [Module]=@mod AND Submodule>@sub
print 'Deleted: '+cast(@@rowcount as varchar(5))
IF @@error<>0 BEGIN
ROLLBACK TRANSACTION
RETURN 8
END
UPDATE #temp SET Submodule=Submodule+1
IF @@error<>0 BEGIN
ROLLBACK TRANSACTION
RETURN 9
END
select * from #temp
INSERT INTO Submodules
SELECT * FROM #temp
IF @@error<>0 BEGIN
ROLLBACK TRANSACTION
RETURN 10
END
END
DROP TABLE #temp
INSERT INTO Submodules (Category, [Module], Submodule, Version, [Description], NA, RA, PA, [Filename])
Values(@cat, @mod, @sub+1, 1, @desc, @na, @ra, @pa, @filename)
IF @@error<>0 OR @@rowcount<>1 BEGIN
ROLLBACK TRANSACTION
IF @@rowcount<>1 Return 2
IF @@error<>0 Return 1
END
select * from submodules where Category=@cat AND [Module]=@mod AND Submodule>=@sub
END
ROLLBACK TRANSACTION
RETURN 0
-
That was dumb. Re-reading my own post, I'm in QA... Of course I can't use RETURN.
Sheesh!!! Virtual Monday.
-
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply