Store Procedure Query

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