Click here to Skip to main content
12,296,779 members (64,818 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL-Server
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]
Posted 10-Nov-12 6:26am
Edited 10-Nov-12 6:38am
Nelek93.9K
v2
Comments
Arindam Tewary 10-Nov-12 12:44pm
   
Whats is exact error message that you get ?
Shanalal Kasim 10-Nov-12 12:48pm
   
provide error details
sumit kausalye 10-Nov-12 23:16pm
   
no error just count_mem which is counter its not incrementing

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

I would suggest not using @@IDENTITY in the where clause. Also I believe that != NULL should be IS NOT NULL.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160525.2 | Last Updated 12 Nov 2012
Copyright © CodeProject, 1999-2016
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