Stored Procedure with Table Value Parameter

Question

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.

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

• HiTech: Thanks for the crazy fast reply. In your SELECT statement you have "column_name". Is that the column name in my TVP?
• Aaron Bertrand: @HiTech Yes. You didn't show how your TVP was defined so I put a generic name there.
• HiTech: 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: I wish i could upvote your answer twice. Thank you for your time and help! That worked perfectly!