Return integer value from 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

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

Return integer value from stored procedure

Author

Topic

just.net
Starting Member

24 Posts

Posted - 2009-04-05 : 10:02:40

hello,
how can i return an integer from stored?
i create a:
ALTER PROCEDURE [dbo].[sp_deleteVaccination]
@vacID int
AS
DECLARE @result int
BEGIN
IF(EXISTS(SELECT VaccinationTypeID FROM dbo.Vaccinations WHERE VaccinationTypeID = @vacID ))
BEGIN
SET @result = 0
END
ELSE
BEGIN
DELETE FROM dbo.VaccinationsTypes WHERE ID = @vacID
SET @result = 1
END
RETURN @result
END

I just wanted to check if the foreign key is in use
and then delete/not delete and return a flag.
in the c# code i create a typed DS and
this stored is working fine and do the work, except the fact
that it always return null value instead of int.
(only in C# its return null)

what could be the problem?

just.net
Starting Member

24 Posts

Posted - 2009-04-05 : 10:56:34

I found the solution:
ALTER PROCEDURE [dbo].[sp_deleteVaccination]
@vacID int
AS
DECLARE @result int
BEGIN
IF(EXISTS(SELECT VaccinationTypeID FROM dbo.Vaccinations WHERE VaccinationTypeID = @vacID))
BEGIN
SET @result = 0
END
ELSE
BEGIN
DELETE FROM dbo.VaccinationsTypes WHERE ID = @vacID
SET @result = 1
END
SELECT @result AS Result
END

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-04-05 : 10:58:34

Looking at your stored procedure, unless there is an exception, @result should be set to 0 or 1. If that is the case, and if your C# code is not receiving that value, the problem is in the C# code, not in the stored procedure.

What the problem is depends on what method you are using. If you are using ADO.Net, make sure you add the return value parameter, and set the direction of the value with a statement such as

RetVal.Direction = ParameterDirection.ReturnValue

If you google for it, you will find examples.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-04-05 : 11:02:59

quote:
Originally posted by just.net

SELECT @result AS Result
END

Then, I guess you were trying to get the data from the record set returned via an ExecuteScalar or ExecuteReader. I assumed you were trying to get the result in a parameter.

just.net
Starting Member

24 Posts

Posted - 2009-05-07 : 15:11:20

yes i want that the SP will return Integer as a type,
the SP return a number as an object, then i am converting the
object to integer. can i return actualy int?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources