Insert into Table use Array in SQL Server 2008

Question

This is my table:

Phone: Id, MemberId, Number, PhoneType, IsDefault

I need an stored procedure to get an array of numbers and a memberId as argument and insert all of them to Phone Table. I can't pass numbers one by one I need to insert all of them or none of them. I think to pass numbers as a list of separated by comma Nvarchar like '0412589, 0425896, 04789652' I must mention MemberId is always constant and pass by argument, PhoneType always constant ='phone', and for first number of array IsDefault = 1 for others IsDefault = 0 How can I select Numbers from numbers and set other columns in insert command? Or you have another suggestion to pass array of numbers?

Solution 1

You could use Table-Valued Parameters. So if you have a type like this:

CREATE TYPE PhoneNumberType AS TABLE 
( number  VARCHAR(50));
GO

Then you procedure would be something like this:

CREATE PROCEDURE dbo. usp_InsertPhoneNumbers
    @TVP PhoneNumberType READONLY
AS 
INSERT INTO Phone(number,....)
SELECT
    number,
    ....
FROM
    @TVP

Reference here

EDIT

It worked just like a table so it is quite easy to do this:

DECLARE @TVP PhoneNumberType

INSERT INTO @TVP
VALUES
    ('0412589'),('0425896'),('04789652')

EXEC usp_InsertPhoneNumbers @TVP

Or if you have it as one string then you need to split it. Here is a neat split function:

CREATE FUNCTION dbo.Split (@s varchar(512),@sep char(1))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  )
GO 

And you call the procedure like this:

DECLARE @TVP PhoneNumberType
DECLARE @textToSplit VARCHAR(512)='0412589, 0425896, 04789652'
INSERT INTO @TVP
SELECT
    split.s
FROM
    dbo.Split(@textToSplit,',') AS split

EXEC usp_InsertPhoneNumbers @TVP

Solution 2

You can pass an XML parameter to your stored procedure.

You would pass in a XML such as:

<phones>
 <phone>
  <memberid>12</memberid>
  <number>123456789</number>
  <type>Landline</type>
 </phone>
 <phone>
  <memberid>12</memberid>
  <number>987654321</number>
  <type>Mobile</type>
 </phone>
</phones>

to a stored procedure like this:

CREATE PROCEDURE dbo.stp_InsertPhoneNumbers
   @numbersXML xml
AS

INSERT INTO Phone(memberid, number, type)
SELECT
   List.Id.query('memberid').value('.', 'int') as memberid,
   List.Id.query('number').value('.', 'varchar(50)') as number,
   List.Id.query('type').value('.', 'varchar(50)') as type
FROM @numbersXML.nodes('/phones/phone') as List(Id)

Comments

[Saeid]

This seems awesome, but I don't understand how can fill @TVP by numbers NVarchar argument?