stored procedure

Hello Narendra,

Before adding values in table you need to check whether values exists or not.

See the below sample

Create PROCEDURE [dbo].[AddUser]
@UserName varchar(50),
@Password varchar(50),
@Email varchar(50),
@ERROR VARCHAR(100) OUT

AS

BEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.               
SET NOCOUNT ON;

---Checking Condition if User exists or not if user not exists returns different message if exists returns different message

IF NOT EXISTS(SELECT * FROM Employee_Information WHERE UserName=@UserName OR Email=@Email )
BEGIN INSERT INTO Employee_Information
(
UserName,
[Password],
Email
)
VALUES
(@UserName,
@Password,
@Email,
)
--If User Successfully Registerd I am returing this Message as Output Parameter
SET @ERROR=@UserName+' Registered Successfully'
END
ELSE
BEGIN--If User already Exists i am returning this Message as Output Parameter
SET @ERROR=@UserName + ' Already Exists'
END
End

Below is a sample -


CREATE PROCEDURE [dbo].[UserInsert]
     ,@FirstName nvarchar(100)
     ,@LastName nvarchar(100)
     ,@EmailAddress nvarchar(500)
     ,@Active bit
     ,@Username nvarchar(100)
     ,@Password nvarchar(100)
     ,@ReturnValue int OUTPUT
AS
BEGIN

SET NOCOUNT ON

BEGIN TRANSACTION

BEGIN TRY
    IF EXISTS(SELECT TOP 1 ID FROM [User] WHERE EmailAddress = @EmailAddress)
    BEGIN
     ROLLBACK TRANSACTION
        SET @ReturnValue = -100
        RETURN @ReturnValue
    END


INSERT [dbo].[User]
     ([FirstName]
     ,[LastName]
     ,[EmailAddress]
     ,[Active]
     ,[Username]
     ,[Password]
     )
VALUES
     (@FirstName
     ,@LastName
     ,@EmailAddress
     ,@Active
     ,@Username
     ,@Password
     )

IF @@ROWCOUNT = 0
BEGIN
     ROLLBACK TRANSACTION
     SET @ReturnValue = 0
     RETURN @ReturnValue
END
ELSE
BEGIN    
     COMMIT TRANSACTION
     SET @ReturnValue = SCOPE_IDENTITY()
     RETURN @ReturnValue
END

END TRY

BEGIN CATCH
     DECLARE @Error_Message varchar(150)
     SET @Error_Message = ERROR_NUMBER() + ' ' + ERROR_MESSAGE()
     ROLLBACK TRANSACTION
     RAISERROR(@Error_Message,16,1)
      SET @ReturnValue = -1
      RETURN @ReturnValue
END CATCH

END

Hi  Narendra,

You can achieve this using output parameter in stored procedure :

find the nice article by Rohatash Kumar   here

http://www.c-sharpcorner.com/UploadFile/rohatash/get-out-parameter-from-a-stored-procedure-in-Asp-Net/

will definitely help you.