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 (fnamelnamemobile)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 (fnamelnamemobile)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(UserNamePasswordFirstNameLastNameEmailMobileNo)VALUES (@UserName@Password@FirstName@LastName@Email@Mobile); Set @Result = 'User successfully added'; end END