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

Pankaj Kumar Choudhary · Answer

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

Chandu Kumawat · Answer

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

how to return some values from insert procedure?

Answers (2)