Click here to Skip to main content
Rate this: bad
good
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
Nelek55.8K
v2
Comments
Arindam Tewary at 10-Nov-12 12:44pm
   
Whats is exact error message that you get ?
Shanalal Kasim at 10-Nov-12 12:48pm
   
provide error details
sumit kausalye at 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)



Advertise | Privacy | Mobile
Web02 | 2.8.141022.2 | Last Updated 12 Nov 2012
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