Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
I want when registration occurs if the side is left then the count should get increment by 1
 
if side is right the count should get increment by 1
 
if both side count is same the sponsor should get increment by 1
 
please please tell me stuck on this code
 
here i have created store procedure please please do tell me where i am going 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,
		   @count_mem varchar(50)= NULL,
		   @smart_pin varchar(100)=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]
,[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,
@renewaldate,
@createddate,
1
 
)
 
END
 

--Pair Matching
SET @count_mem=0
WHILE(@sponcorid!=NULL)
	BEGIN
		IF(@regCode !=NULL)
		BEGIN
			DECLARE @countl int
			IF(@side ='LEFT')
			BEGIN
				UPDATE Registration_Master
				SET @count_mem=@count_mem+1
				WHERE regCode=@regCode AND side='LEFT' AND count_mem=@count_mem
		
			END
			DECLARE @countr int
			IF(@side='RIGHT')
			BEGIN
				UPDATE Registration_Master
				SET @count_mem=@count_mem+1
				WHERE regCode=@regCode AND side='RIGHT' AND count_mem=@count_mem
			END
			IF(@countl=@countr)
			BEGIN
				UPDATE Registration_Master
				SET @count_mem=@count_mem+1
				WHERE regCode=@regCode
				IF(@regCode!=NULL)
				BEGIN
					UPDATE Registration_Master
					SET @count_mem=@count_mem+1
					WHERE sponcorid=@sponcorid AND count_mem=@count_mem
				END
			END
			
		END
 
	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
Posted 9-Nov-12 0:00am
Edited 9-Nov-12 0:03am
v2

1 solution

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

Solution 1

A couple of devices for illustration. A table.
CREATE TABLE [cpqaAnswers].[cpqa].[tbl_SK_Registration_Master](
	   [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,[count_mem] varchar(50) NULL,[smart_pin] varchar(100) NULL,[createddate] datetime  NULL, [is_activate] bit NULL,	[registrationid] bigint  NULL,[renewaldate] datetime NULL,[regCode] varchar(50) NULL
	   )
And a new schema. Then the above stoproc.
--CREATE SCHEMA [cpqa]
 
USE[cpqaAnswers]
GO
CREATE PROCEDURE [cpqa].[sp_SK_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,@count_mem varchar(50)= NULL,	@smart_pin varchar(100)=NULL, @createddate datetime= NULL, @is_activate bit= NULL,	@registrationid bigint= NULL,@renewaldate datetime=NULL,@regCode varchar(50)=NULL
      )
AS
	BEGIN
	 INSERT INTO tbl_SK_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],[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,	@renewaldate,@createddate,1
	 						)
	END
	--Pair Matching
	SET @count_mem=0
	WHILE(@sponcorid!=NULL)
		BEGIN
			IF(@regCode !=NULL)
			BEGIN
				DECLARE @countl int
				IF(@side ='LEFT')
				BEGIN
					UPDATE tbl_SK_Registration_Master
					SET @count_mem=@count_mem+1
					WHERE regCode=@regCode AND side='LEFT' AND count_mem=@count_mem
			
				END
				DECLARE @countr int
				IF(@side='RIGHT')
				BEGIN
					UPDATE tbl_SK_Registration_Master
					SET @count_mem=@count_mem+1
					WHERE regCode=@regCode AND side='RIGHT' AND count_mem=@count_mem
				END
				IF(@countl=@countr)
				BEGIN
					UPDATE tbl_SK_Registration_Master
					SET @count_mem=@count_mem+1
					WHERE regCode=@regCode
					IF(@regCode!=NULL)
					BEGIN
						UPDATE tbl_SK_Registration_Master
						SET @count_mem=@count_mem+1
						WHERE sponcorid=@sponcorid AND count_mem=@count_mem
					END
				END
			END
		END
	 
	SELECT     [registrationid], [regCode],  [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], [count_mem],[smart_pin],[createddate], [is_activate]
	FROM         tbl_SK_Registration_Master
	 
	WHERE [registrationid]=@@IDENTITY
There were errors ... in the above SELECT; there was an extra field called "[username]" (which i took the liberty of removing) ... either reduce the assembly by deletion or complicate it by addition. Take 'yer pick.
 
Then a quick context menu EXECUTE TO clipboard. And a paste.
DECLARE @RC int
DECLARE @fname varchar(50)
DECLARE @mname varchar(50)
DECLARE @lname varchar(50)
DECLARE @p_password varchar(50)
DECLARE @question varchar(50)
DECLARE @answer varchar(50)
DECLARE @person_address varchar(50)
DECLARE @gender varchar(50)
DECLARE @sponcorid varchar(50)
DECLARE @dob datetime
DECLARE @productname varchar(50)
DECLARE @payment varchar(50)
DECLARE @sponcorname varchar(50)
DECLARE @side varchar(50)
DECLARE @mobile varchar(50)
DECLARE @bankname varchar(50)
DECLARE @baccno varchar(50)
DECLARE @bankbranch varchar(50)
DECLARE @PAN varchar(50)
DECLARE @IFSC_code varchar(50)
DECLARE @email_id varchar(50)
DECLARE @member_status varchar(50)
DECLARE @flag bit
DECLARE @count_mem varchar(50)
DECLARE @smart_pin varchar(100)
DECLARE @createddate datetime
DECLARE @is_activate bit
DECLARE @registrationid bigint
DECLARE @renewaldate datetime
DECLARE @regCode varchar(50)
 
/* There's a little hypothetical data ... */
 
SET @fname		= 'John'
SET @mname		= 'Jacob'
SET @lname		= 'Jingleheimerschmidt'
SET @p_password		= 'daredevil'
SET @question		= 'how many hooks am I holding up'
SET @answer		= 'don''t know because I''m wearing a blindfold'
SET @person_address	= '1313 Mockingbird Lane, Sandusky, OH 43456'
SET @gender		= 'male'
SET @sponcorid		= 'tv7'
SET @dob		= '07/09/36'
SET @productname	= 'SQL Server Managment Studio 2008 64-bit Express'
SET @payment		= 'granitemarbleolivinequartz'
SET @sponcorname	= 'SPON Corp America'
SET @side		= 'muttonFull'
SET @mobile		= '918-105-4378'
SET @bankname		= 'Sanwa'
SET @baccno		= 'seriouslymicroscopicorganismshaveRIFDtagsthesedays'
SET @bankbranch	        = '8722'
SET @PAN		= '100100001000111000010001'
SET @IFSC_code		= 'SNWA0008722'
SET @email_id		= 'millet@utumno.uk'
SET @member_status	= '523'
SET @flag		= '1'
SET @count_mem		= '128'
SET @smart_pin		= '0'
SET @createddate	= '11/11/77'
SET @is_activate	= '1'
SET @registrationid     = 9473553
SET @renewaldate	= '01/01/00'
SET @regCode		= 'sansouci7sushi6sus4s0'
 
EXECUTE @RC = [cpqaAnswers].[cpqa].[sp_SK_insert_registration] 
   @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,@count_mem,@smart_pin,@createddate,@is_activate,@registrationid,@renewaldate,@regCode
GO
And a final SELECT to reach OVER.
SELECT [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],[count_mem],[smart_pin],[createddate],[is_activate],[registrationid],[renewaldate],[regCode]
					FROM [cpqaAnswers].[cpqa].[tbl_SK_Registration_Master]
GO
  Permalink  
v2

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

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Shai Vashdi 1,448
1 Tadit Dash 270
2 OriginalGriff 223
3 Sergey Alexandrovich Kryukov 215
4 Peter Leow 180
0 Sergey Alexandrovich Kryukov 9,440
1 OriginalGriff 5,618
2 Peter Leow 4,280
3 Maciej Los 3,540
4 Abhinav S 3,363


Advertise | Privacy | Mobile
Web02 | 2.8.140415.2 | Last Updated 8 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid