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.

1 Answer 1


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.)

  • Thanks for the crazy fast reply. In your SELECT statement you have "column_name". Is that the column name in my TVP?
    – HiTech
    Apr 10, 2015 at 19:11
  • @HiTech Yes. You didn't show how your TVP was defined so I put a generic name there. Apr 10, 2015 at 19:20
  • That was extremely clean and easy to implement. Thank you very much. You wouldn't happen to have a suggestion on how i can check my DB to see if a Serial number that's in the TVP already exists?
    – HiTech
    Apr 10, 2015 at 21:14
  • I wish i could upvote your answer twice. Thank you for your time and help! That worked perfectly!
    – HiTech
    Apr 10, 2015 at 23:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.