CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Razzle Dazzle
Razzle Dazzle

Reputation: 521

INSERTing a list of values received as input

So I have a query that needs to take input from an outside application and use those values to insert into a table variable.

DECLARE @prompt TABLE(
answerID int,
rowid int NOT NULL IDENTITY(1,1) primary key
);
INSERT INTO @HELLO (answerid)
SELECT (x,y,z)

The problem is that when the input is received it comes in as a list as so:

 INSERT INTO @HELLO (answerid)
 SELECT (4,55,66,88,978)

Which is incorrect syntax. The list comes in explicitly that form of x,y,z so there isn't really a way for me to massage the data for something like:

INSERT INTO #blah
VALUES (x), (y), (z)

Or is there? In the end, I just need to get this list of values into a table so I can perform work on them.

Upvotes: 2

Views: 4534

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

If the list is passed in from C# etc. and is sourced from a collection (say a DataTable), then the most efficient way to deal with this list is to use a table-valued parameter. First, create a table type:

CREATE TYPE dbo.MyList(ID INT PRIMARY KEY);

Now, create a stored procedure that accepts this type as a parameter:

CREATE PROCEDURE dbo.MyProcedure
  @List dbo.MyList READONLY
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @prompt TABLE
  (
    answerID INT,
    [rowid] INT NOT NULL IDENTITY(1,1) PRIMARY KEY
  );

  INSERT INTO @HELLO (answerid)
    SELECT ID FROM @List;

  ...
END
GO

Sample usage from T-SQL:

DECLARE @List dbo.MyList;

INSERT @List VALUES(4),(55),(66),(88),(978);

EXEC dbo.MyProcedure @List = @List;

From C# etc. you can pass a DataTable directly as a parameter so you don't have to worry about all this transposition.

If you can't deal with that, then create a Split function.

CREATE FUNCTION dbo.SplitInts
(
   @List       VARCHAR(MAX),
   @Delimiter  VARCHAR(255) = ','
)
RETURNS TABLE
WITH SCHEMABINDING 
AS
  RETURN 
  (  
    SELECT Item = y.i.value('(./text())[1]', 'int')
    FROM 
    ( 
      SELECT x = CONVERT(XML, '<i>' 
        + REPLACE(@List, @Delimiter, '</i><i>') 
        + '</i>').query('.')
    ) AS a CROSS APPLY x.nodes('i') AS y(i)
  );
GO

Now you can say:

DECLARE @prompt TABLE
(
  answerID INT,
  [rowid] INT NOT NULL IDENTITY(1,1) PRIMARY KEY
);

INSERT @prompt(answerID) 
  SELECT Item FROM dbo.SplitInts('4,55,66,88,978', ',');

Upvotes: 2

Related Questions