how to return some values from insert procedure?

Question

Dear all ,

 

i have a proc like bellow ,

 

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

as 

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

Answer 1

Hello @Prasad you can use ROWCount for you query.

 

Re Write your procedure as below

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

as

begin

BEGIN TRY

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

IF @@ROWCOUNT=1

BEGIN

SELECT 1 AS StatusInd, 'Inserted' AS StatusMsg

End

ELSE

begin

SELECT 0 AS StatusInd, 'Not Inserted' AS StatusMsg

END

END TRY

BEGIN CATCH

SELECT 0 AS StatusInd, 'Not Inserted' AS StatusMsg

END CATCH

END

 

Answer 2

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