-2

I have a table student(int ID, nvarchar(10) studentName) and a table user type myStudentType (int ID, nvarchar(10) studentName)

I need to create a stored procedure that accepts a myStudentType parameter (@students) and a parameter @newname 

mySp(@students myStudentType, @newname nvarchar(10))

and merges myStudentType with student so that all the rows with ids that exists on both tables will now in student table have for the studentName - @newname (the parameter) and the sp will return a list of all the records from @students that were not updated in the student table

What is the best way to do that (preferred with merge)

thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Billybollt Billybollt
  • 1
  • i have only the part using the merge to update all the rows in students table... i dont know how to get all the recordes that were not updated in the param to the sp – Billybollt Billybollt Dec 19 '11 at 05:17

1 Answers1

0

The part of the spec that I think will be most problematic is:

the sp will return a list of all the records from @students that were not updated in the student table

I assume by 'list' the spec implies 'table' (T-SQL has no concept of a 'list' type). An output parameter cannot be a TABLE type (including user-defined table types). While a function can return a table, you cannot update base tables via a function. For more details and possible solutions, see How to Share Data Between Stored Procedures by by Erland Sommarskog.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138