how to return some values from insert procedure?

Prasad Bhagat

10y886

Dear all ,

i have a proc like bellow ,

create proc insertdata(@fname varchar( 100),@lname varchar(100),@mobile varchar(100)

begin

insert into emp (fname,lname,mobile)values (@fname,@lname,@mobile)

end

as of now perfect ,

now i want to return if the proc is executed succesfully then i want to retun two varables like

StatusInd=1 and StatusMsg="Inserted"

else

StatusInd=-1 and StatusMsg="NoInserted"

so please help me how can i create proc using sql server

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

use output parameter to set msg

like this

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[insertUser]

@UserName varchar(50),

 @Password varchar(50),

@FirstName varchar(50),

@LastName varchar(50),

@Email varchar(150),

@Mobile varchar(50),

 @Result varchar(100) output )

AS

BEGIN

Declare @UserCount int;

 SELECT @UserCount = COUNT(*) FROM users WHERE UserName = @UserName;

IF(@UserCount > 0) begin

  Set  @Result = 'User already exists';

end

 ELSE

begin

  INSERT INTO users(UserName,Password,FirstName,LastName,Email,MobileNo)VALUES (@UserName,@Password,@FirstName,@LastName,@Email,@Mobile);

   Set  @Result = 'User successfully added';

end

END