Brett Kaiser (x002548) Blog

Seems this question comes up quite a bit, as it did here.  And then since my boss also was asking me how this is done, I'd figure that I'd post a solution.  It involves forming a comma delimeted string and passing that in to the stored procedure.  Modifying a Bill Graziano User Defined Function, we simply pass in this comma delimeted string.  Now I solved the posters (mester) based on the original Requirements, but I do take exception to the table structures.  They indicate they have a Reseller table, and Company table, and a Junction Table.  While this may be the correct logical model, I would think I would eliminate the juction table.  Kinda like a Person Table, and a Phone Table.  Why bother with a PersonPhone Table.  What would be the sense.  Is it worth the effort to maintain that, or to try enforce RI?  If we make that assumption and lose the junction table, then the cursor in the code below is replaced with a very nice set based insert into the child table.  Clear as mud?  Here's the code sample, and Thanks again Bill (I had to modify my ThankyouBillCounter from int to bigint).

I'd appreciate any Pro's Cons about this approach, and any thoughts you may have.  Thanks for reading

PS I used IDENTITY here because it makes it a little more dicey.  What, no IDENTITY you say?  Then your job just got easier, and to reiterate, with out the juction table, it really easy.

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE Reseller(
   R_ID int IDENTITY(1,1) PRIMARY KEY
 , R_Detail varchar(50))
CREATE TABLE Company(
   C_ID int IDENTITY(1,1) PRIMARY KEY
 , C_Detail varchar(50))
GO

CREATE TABLE Reseller_Company (
   R_ID int
 , C_ID int
 , CONSTRAINT Company_Reseller_PK  PRIMARY KEY (R_ID, C_ID)
 , CONSTRAINT Company_Reseller_FK1 FOREIGN KEY (R_ID) REFERENCES Reseller (R_ID)
 , CONSTRAINT Company_Reseller_FK2 FOREIGN KEY (C_ID) REFERENCES Company  (C_ID)
)
GO

CREATE FUNCTION dbo.CSVToParse (
 @Array varchar(8000)
)
RETURNS @ParseTable table
 (ParseValue varchar(50))
AS

-- Code Originally (CSVTOint) Written by Bill Graziano, 10/13/2002
-- http://www.sqlteam.com/item.asp?ItemID=11499

  BEGIN
 DECLARE @separator char(1), @separator_position int, @array_value varchar(1000)
  SELECT @separator = ',', @array = @array + ','
 
 WHILE patindex('%,%' , @array) <> 0
   BEGIN
    SET @separator_position =  patindex('%,%' , @array)
   SET @array_value = left(@array, @separator_position - 1)
  INSERT @ParseTable(ParseValue) VALUES (@array_value)
    SET @array = stuff(@array, 1, @separator_position, '')
   END
 RETURN
  END
GO

CREATE PROC usp_INS_Company_Resellers (
   @R_Detail varchar(50)
 , @C_Details varchar(1000)
)
AS
SET NOCOUNT ON
 DECLARE @R_SCOPE_IDENTITY int, @C_SCOPE_IDENTITY int, @C_Detail varchar(50)

 INSERT INTO Reseller(r_Detail) SELECT @R_Detail 
 SELECT @R_SCOPE_IDENTITY = SCOPE_IDENTITY()

 DECLARE Companies CURSOR FOR SELECT ParseValue FROM dbo.CSVToParse(@C_Details)
 OPEN Companies 
 FETCH NEXT FROM Companies INTO @C_Detail
 WHILE @@FETCH_STATUS = 0
   BEGIN

   INSERT INTO Company(C_Detail) SELECT @C_Detail
  SELECT @C_SCOPE_IDENTITY = SCOPE_IDENTITY()

  INSERT INTO Reseller_Company (R_ID, C_ID) SELECT @R_SCOPE_IDENTITY, @C_SCOPE_IDENTITY 
  FETCH NEXT FROM Companies INTO @C_Detail
   END
 CLOSE Companies
 DEALLOCATE Companies
SET NOCOUNT OFF
GO

EXEC [dbo].[usp_INS_Company_Resellers] 'Reseller name...whatever','Company1,Company2,Companty3'
GO

SELECT * FROM Reseller
SELECT * FROM Company
SELECT * FROM Reseller_Company
GO
 
SET NOCOUNT ON
DROP FUNCTION CSVToParse
DROP PROC usp_INS_Company_Resellers
DROP TABLE Reseller_Company, Reseller, Company
GO