2

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.

10

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 '15 at 19:11
  • @HiTech Yes. You didn't show how your TVP was defined so I put a generic name there. – Aaron Bertrand Apr 10 '15 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 '15 at 21:14
  • I wish i could upvote your answer twice. Thank you for your time and help! That worked perfectly! – HiTech Apr 10 '15 at 23:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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