stored procedure

Question

i want to write a stored procedure which will not allowed to insert duplicate value on two columns or multiples columns in sql server ,and at the time generating error how could i get that error on web page label

Abhay Shanker · Answer

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

Nitesh Kejriwal · Answer

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

Anupam Singh · Answer

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/ w ill definitely help you.

stored procedure

Answers (3)