April 2, 2009 at 6:40 am
I have a procedure which shuld return an int depending on Insertion done but it is not returning any thing though i have written Return statement also
Here is my Procedure
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[UserRoleMapping]
@userProgrammeId int,@roleProgrammeId int,@createdBy int,@createdDate datetime,@loggedInAs int
as
begin
declare @out int
declare @return int
set nocount on
begin try
begin tran UserRoleMapping
Insert Into User_Roles(Fk_users,Fk_Roles,CreatedBy,CreatedDate,LoggedInAs)
values(@userProgrammeId,@roleProgrammeId,@createdBy,@createdDate,@loggedInAs)
commit tran UserRoleMapping
set @return=1
end try
begin catch
IF (XACT_STATE()) = -1
BEGIN
ROLLBACK TRAN UserRoleMapping
set @return=0
END
ELSE IF (XACT_STATE()) = 1
BEGIN
--it now depends on the type of error or possibly the line number
--of the error
IF ERROR_NUMBER() = 547
BEGIN
ROLLBACK TRAN UserRoleMapping
set @return=0
END
ELSE IF ERROR_NUMBER() = 2627
BEGIN
ROLLBACK TRAN UserRoleMapping
set @return=0
END
END
END CATCH
return @return
set nocount off
end
Can any one please tell me y it is not returning value as it is very urgent to me
/********************************************************
Technology is just a tool,in terms of getting the kids working together and motivating them,the teacher is the most important.
********************************************************/
April 2, 2009 at 7:00 am
Well, it depends on how you are calling it. If you are doing something like this:
Exec [dbo].[UserRoleMapping] parameter list
Then you will not get anything returned as this type of call is just looking for a result set to be returned and you are not returning a result set. If you want to get the return value which you are setting you need to do this:
Declare @ReturnValue Int
Exec @ReturnValue = [dbo].[UserRoleMapping] parameter list
Select @ReturnValue
If you are using .NET to call the procedure then you need to use a Parameter with a type of ReturnValue to get the return value.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply