April 29, 2005 at 10:48 am
We're writing a .Net app that executes the following procedure as a Command object of a SQL Connection:
CREATE PROCEDURE [dbo].[Insert_Department]
(
@dnum char(3)
)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRAN INSERTING
SELECT * FROM Departments WHERE dept_number = @dnum
IF @@ROWCOUNT>0 BEGIN
ROLLBACK TRAN INSERTING
RETURN 10
END
INSERT INTO Departments (dept_number) VALUES(@dnum)
IF @@ERROR<>0 BEGIN
ROLLBACK TRAN INSERTING
RETURN 9
END
COMMIT TRAN INSERTING
RETURN 0
END
GO
Two parameters are defined, the RETURN_VALUE parameter and the input parameter.
When the program (C#) looks at Parameters(0) (or Parameters("RETURN_VALUE")) it is seeing a NULL. How can that be? The SP is doing the Insert (we checked the table) so shouldn't I be seeing a 0?
We use exactly the same code (just change the name of the SP in the command text) running the following SP:
CREATE PROCEDURE [dbo].[delete_department]
(
@dnum char(3)
)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRANSACTION DELETING
INSERT INTO Deleted_Departments (dept_number) values(@dnum)
IF @@error <> 0 BEGIN
ROLLBACK TRANSACTION DELETING
RETURN 1
END
DELETE FROM Departments where dept_number = @dnum
IF @@error<>0 BEGIN
ROLLBACK TRANSACTION DELETING
RETURN 2
END
COMMIT TRANSACTION DELETING
RETURN 0
END
GO
And it sees a 0 in the RETURN_VALUE Parameter. I would think the exact same code running two different SP's that are virtually identical would both return a 0 when they work...
I'm confused...
April 29, 2005 at 11:27 am
I think that the select in the first proc is causing the problem. Try removing the select, then in the if statement put an if exists (removed select stmnt)
April 29, 2005 at 11:54 am
That was exactly it.
Thanks.
April 29, 2005 at 11:56 am
HTH.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply