Click here to Skip to main content

sumit kausalye asked:

Open original thread
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

Preview



When answering a question please:
  1. Read the question carefully.
  2. Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
  3. If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Let's work to help developers, not make them feel stupid.
Please note that all posts will be submitted under the The Code Project Open License (CPOL).



Advertise | Privacy | Mobile
Web02 | 2.8.141022.2 | Last Updated 26 Mar 2009
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100