SQL Server – Using Stored Procedure with Table Value Parameter

sql serverstored-procedures

I'm attempting to write a stored procedure that will have two parameters.

  1. ComputerName | nvarchar
  2. Monitor_Serial | Table-valued parameter (TVP)

I'm using a TVP because some computers have an unknown number of monitors.

I would like my stored procedure to take each serial number in the Monitor_Serial and insert a new line in my table with the associated ComputerName (exactly how a foreach loop would work in programing).

How would I do this? When I was doing some research I saw the use of a cursor mentioned a few times but I'm not sure if this is what I should be using. Any suggestions would be greatly appreciated.

Best Answer

A cursor should very rarely be your first approach. Especially if you've gone to the trouble of making a table type; the primary goal there is to avoid row-by-row processing, splitting strings, etc.

Making some guesses about the destination table, its columns, and the name of the column in your TVP, you can insert multiple rows in the destination table by using INSERT/SELECT:

INSERT dbo.destination_table
(
  computer_name, serial_number
)
SELECT @ComputerName, column_name
  FROM @Monitor_Serial;

To make sure you only insert new serial numbers, use:

  FROM @Monitor_Serial AS m
  WHERE NOT EXISTS (SELECT 1 FROM dbo.destination_table
    WHERE serial_number = m.column_name
    -- AND computer_name = @ComputerName
  );

(You may or may not need that commented line, since it's unclear if you want to prevent any duplicates overall or just for a specific computer name.)

Related Question