dynamically enter records 1

[techmaniac15]

I'm trying to have a stored procedure that enters the information for a golf registration. I'd like to dynamically add the players based on how many they registered. So either 2,3 or 4. The code for the SP:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go




ALTER procedure [dbo].[Golf_Registration_Add]
@paramOrgName varchar(150)=null,
@paramContactPerson varchar(75)=null,
@paramStreetAddress varchar(150)=null,
@paramCity varchar(50)=null,
@paramState varchar(50)=null,
@paramZip varchar(10)=null,
@paramEmail varchar(50)=null,
@paramPhone varchar(15)=null,
@paramOrderNumber varchar(20)=null,
@paramRegistrationType int=null,
@paramACSDollars varchar(5)=null,
@paramACSContribution int=null,
@paramGolfers int=null,
@paramPlayerName1 varchar(50)=null,
@paramPlayerName2 varchar(50)=null,
@paramPlayerName3 varchar(50),
@paramPlayerName4 varchar(50),
@paramPlayerName5 varchar(50),
@paramUserName varchar(50)=null,
@paramLogon varchar(8)=null


as

declare @regID int;
declare @gtRegID int;
declare @regDate smalldatetime;
declare @golfName char(16);
declare @gNum varchar(1);

SET @regDate = GETDATE();

--begin tran

begin
INSERT INTO gtContact(Organization, ContactPerson, Address, City, State, Zip, Email, Phone, adId)

VALUES(@paramOrgName, @paramContactPerson, @paramStreetAddress, @paramCity, @paramState, @paramZip, @paramEmail, @paramPhone, NULL);

if @@ERROR<>0 GOTO Error_Handling;
set @gtRegID=@@IDENTITY;
end
begin
INSERT INTO gtRegistration(rTypeID, Contribution, ContributionAmt, InvoiceNumber, regDate)

VALUES(@paramRegistrationType, @paramACSContribution, @paramACSDollars, @paramOrderNumber, @regDate);

--if @@ERROR<>0 GOTO Error_Handling;
set @regID=@@IDENTITY;
end
begin
INSERT INTO gtConXReg(gtID,gtRegID)

VALUES(@gtRegID,@regID);

if @@ERROR<>0 GOTO Error_Handling;
end

if (@paramGolfers) > 0
While @paramGolfers > 0
Set @gNum = CONVERT(varchar,@paramGolfers)
Set @golfName = '@paramPlayerName' + '' + @gNum;
begin
INSERT INTO gtPlayers(PlayerName,gtID)
VALUES(@golfName,@gtRegID);

if @@ERROR<>0 GOTO Error_Handling;
Set @paramGolfers = @paramGolfers - 1
end
begin
INSERT INTO gtReg_Logon(Username, Login, gtRegID)
VALUES(@paramUsername, @paramLogon, @regID);
end
--commit tran
return(0);

Error_Handling:
begin
RAISERROR ('Transaction rolled back.', 16, 1);
ROLLBACK TRAN;
end

Of significant importance is the @paramgolfers section where I'm trying to get the total number of golfers and add records accordingly. Assistance is appreciated.

 

[techmaniac15]

Wow... NOBODY?

 

[Peager]

You might want to pass the player's names in as a CSV string and use a 'split' function to parse them into a table, inserting those values along with your other parameters into your DB.

/*
 =============================================
 Author:		Paul Richmond
 Create date:	02/06/2008
 Description:	Converts a Value String into a table.
 
 Test Script	SELECT * FROM dbo.Util_ConvStrToTable('WS,SP,DF',',')
 =============================================
*/
ALTER FUNCTION [dbo].[Util_ConvStrToTable] (
    @RepParam VARCHAR(MAX)
   ,@Delim CHAR(1) = ',')
RETURNS @VALUES TABLE (Param VARCHAR(50))
AS BEGIN
    DECLARE @chrind INT
    DECLARE @Piece NVARCHAR(4000)
    SELECT  @chrind = 1
    WHILE @chrind > 0
        BEGIN
            SELECT  @chrind = CHARINDEX(@Delim,@RepParam)
            IF @chrind > 0 
                SELECT  @Piece = LEFT(@RepParam,@chrind - 1)
            ELSE 
                SELECT  @Piece = @RepParam
            INSERT  @VALUES (Param)
            VALUES  (@Piece)
            SELECT  @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
            IF LEN(@RepParam) = 0 
                BREAK
        END
    RETURN
   END
[code/]

Begin a table valued function, you can select from it, join it, etc.

 

[techmaniac15]

Thanks, I've used this technique now and it's working as planned.

Your response is highly appreciated.