why my code is not working properly

here my store procedure but its not working please tell me where i go wrong

ALTER PROCEDURE [dbo].[sp_insert_registration]
	(      
		  
		   @fname varchar(50)= NULL,
		   @mname varchar(50)= NULL,
		   @lname varchar(50)= NULL,
		   @p_password varchar(50)= NULL,
		   @question varchar(50)=NULL,
		   @answer varchar(50)=NULL,
           @person_address varchar(50)= NULL,
           @gender varchar(50)= NULL,
           @sponcorid varchar(50)= NULL,
		   @dob datetime= NULL,
			@productname varchar(50)=NULL,
		   @payment varchar(50)= NULL,
           @sponcorname varchar(50)= NULL,
		   @side varchar(50)= NULL,
           @mobile varchar(50)= NULL,
           @bankname varchar(50)= NULL,
           @baccno varchar(50)= NULL,
           @bankbranch varchar(50)= NULL,
           @PAN varchar(50)= NULL,
           @IFSC_code varchar(50)= NULL,
           @email_id varchar(50)= NULL,
           @member_status varchar(50)= NULL,
           @flag bit=NULL,
		   @smart_pin varchar(100)=NULL,
		   @count_mem int=NULL,
           @createddate datetime= NULL,
           @is_activate bit= NULL,
			@registrationid int= NULL,
			@renewaldate datetime=NULL,
			@regCode varchar(50)=NULL
           )
AS
BEGIN

	INSERT INTO Registration_Master
           (
[fname]
,[mname]
,[lname]
,[p_password]
,[question]
,[answer]
,[person_address]
,[gender]
,[sponcorid]
,[dob]
,[productname]
,[payment]
,[sponcorname]
,[side]
,[mobile]
,[bankname]
,[baccno]
,[bankbranch]
,[PAN]
,[IFSC_code]
,[email_id]
,[member_status]
,[flag]
,[smart_pin]
,[count_mem]
,[renewaldate]
,[createddate]
,[is_activate]
           )

     VALUES
           (

@fname,
@mname,
@lname,
@p_password,
@question,
@answer,
@person_address,
@gender,
@sponcorid,
@dob,
@productname,
@payment,
@sponcorname,
@side,
@mobile,
@bankname,
@baccno,
@bankbranch,
@PAN,
@IFSC_code,
@email_id,
@member_status,
@flag,
@smart_pin,
@count_mem,
@renewaldate,
@createddate,
1

)

END

-- i have used function for generate auto increment alpha numeric code this code --is working properly no issue

DECLARE @regid int
SET @regid=@@IDENTITY
DECLARE @RegistrationCode varchar(100)
DECLARE registrationcodeCursor CURSOR FOR
SELECT [dbo].[RegistrationCode] 
(
   'P4U',@fname,@mname,@lname,'000',@regid,'34',@dob
)
   OPEN registrationcodeCursor
   FETCH FROM registrationcodeCursor INTO @RegistrationCode
   CLOSE registrationcodeCursor
   DEALLOCATE registrationcodeCursor
 
UPDATE Registration_Master
SET regCode=@RegistrationCode
WHERE registrationid=@regid

--here again i have used function to take full name even this is working properly

DECLARE @regid1 int
SET @regid1=@@IDENTITY
DECLARE @usernm varchar(50)
DECLARE usernameCursor CURSOR FOR
SELECT [dbo].[fullusername]
(
	@fname,' ',@mname,' ',@lname
)
OPEN usernameCursor
FETCH FROM usernameCursor INTO @usernm
CLOSE usernameCursor
DEALLOCATE usernameCursor

UPDATE Registration_Master
SET username=@usernm
WHERE registrationid=@regid1

--Pair Matching

--this code is not working properly

SET @count_mem=0

WHILE(@sponcorid !=NULL)
	BEGIN
		IF(@count_mem<2)
		BEGIN
			SET @count_mem=@count_mem+1
			UPDATE Registration_Master SET regCode=@regCode,count_mem=@count_mem WHERE side=@side
		END
		ELSE IF(@count_mem=2)
		BEGIN
			SET @count_mem=@count_mem+1

			UPDATE Registration_Master SET sponcorid=@sponcorid,count_mem=@count_mem
		END
		DECLARE @temp varchar(50)
		SET @temp=@sponcorid
		
		SET @count_mem=@count_mem+1

		UPDATE Registration_Master SET sponcorid=@sponcorid WHERE regCode=@temp AND count_mem=@count_mem
	END

SELECT     registrationid, regCode,  fname,mname,lname,username, p_password,question,answer, person_address, gender, sponcorid, dob,productname, payment, sponcorname, side, mobile, bankname, baccno, 
                      bankbranch, PAN, IFSC_code, email_id, member_status, count_mem,smart_pin,createddate, is_activate
FROM         Registration_Master

WHERE registrationid=@@IDENTITY

[edit]code blocks added[/edit]

Tags: SQL Server