Stored procedure Help Insert with a one to many relationship... 2

[prover]

I need some advise. I have a person table and a language table with a one to many relationship. I want to be able to add a new person and if they speak ohter languages then add then to the lang. table.

The data is comming from an asp.net page. I'd rather not insert the person , return the new id number then submit any languages spoken. i'd like to keep it to one round trip to the DB. Should i pass the any languages spoken as a delimited string and loop throught that in a stored procedure? and if so how?

Person Table:
PerID
FirstName
LastName
etc...

Language Table:
PerID
LangID (id number from a lookup table)

 

[gmmastros]

I suppose you want to send a comma delimited list if integers representing the list of languages. If this is the case, then I would suggest that you use the following function to split this comma delimited list. The results will be a table that you can use to insert in to the 2nd table.

[COLOR=blue]ALTER[/color]     [COLOR=#FF00FF]Function[/color] Split(@CommaDelimitedFieldNames [COLOR=blue]Varchar[/color](8000),@SplitChar [COLOR=blue]VarChar[/color](10))  
Returns @Tbl_FieldNames [COLOR=blue]Table[/color]  (FieldName [COLOR=blue]VarChar[/color](8000) [COLOR=blue]primary[/color] [COLOR=blue]key[/color])  [COLOR=blue]As[/color]  

[COLOR=blue]Begin[/color] 
 [COLOR=blue]Set[/color] @CommaDelimitedFieldNames =  @CommaDelimitedFieldNames + @SplitChar

 [COLOR=blue]Declare[/color] @Pos1 [COLOR=blue]Int[/color]
 [COLOR=blue]Declare[/color] @pos2 [COLOR=blue]Int[/color]
 
 [COLOR=blue]Set[/color] @Pos1=1
 [COLOR=blue]Set[/color] @Pos2=1

 [COLOR=blue]While[/color] @Pos1<Len(@CommaDelimitedFieldNames)
 [COLOR=blue]Begin[/color]
  [COLOR=blue]Set[/color] @Pos1 = [COLOR=#FF00FF]CharIndex[/color](@SplitChar,@CommaDelimitedFieldNames,@Pos1)
  [COLOR=blue]Insert[/color] @Tbl_FieldNames [COLOR=blue]Select[/color]  [COLOR=#FF00FF]Cast[/color]([COLOR=#FF00FF]Substring[/color](@CommaDelimitedFieldNames,@Pos2,@Pos1-@Pos2) [COLOR=blue]As[/color] [COLOR=blue]VarChar[/color](8000))
  [COLOR=blue]Set[/color] @Pos2=@Pos1+1
  [COLOR=blue]Set[/color] @Pos1 = @Pos1+1
 [COLOR=blue]End[/color] 
 [COLOR=blue]Return[/color]
[COLOR=blue]End[/color]

To see how this works...

Select * From dbo.Split('1,2,5,10', ',')

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLSister]

YOu can also have two insert statements inthe proc, one using the split to get the dat and one which does not need it to insert into the parent table.

You may also consider putting the split part in a separate sp that is called from the main sp, then it can be used to onsert new languages only when they are added and the parent aready existed.

Questions about posting. See faq183-874

 

[gmmastros]

Good point SQLSister. I feel as though I only gave 1/2 an answer (after reading your response).

Here's what I would do...

1. Create an SP to insert people. This SP should have a varchar(8000) parameter that represents the comma delimited list of languages.

2. In this SP, insert the data in to the person table, also while extracting the unique identifier using Scope_Identity()

3. Create another SP to insert languages. This SP would have 2 parameters, PersonId and a comma delimited list of languages.

4. In this SP, use the split function (shown above) and the PersonId to insert values in to the language table.

If there is anything about these steps that you don't understand or would like clarification on, don't hesitate to ask.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[prover]

Thank you both!

1. I was thinking the same thing two separate sp. one to add the person the other the languages (if any). Its nice to have confirmation.

2. The code to split the delimited string. Wow thats going save a lot of time!

I just love Tek Tips and the experts helping us!!!