Stored Procedure for Updating a Column in Sql Server

Question

I have a requirement to update a column with multiple values. The query looks like below.

Update table1 set column1 = (
  select value from table2 where table1.column0 = table2.coulmn
)

Is there any generalised stored procedure for a requirement like the above?

Answer 1

short of creating a statement as a string and using the "execute" statement, I don't know of one. Generally "execute" is frowned on as it's a potential injection attack point.

Answer 2

Why would you want to update one table with information that is easily available in another? Seems like you are just guaranteeing that you are going to have to run this query every single time you perform an update, insert or delete against the camsnav table. Otherwise how are you going to keep them in sync?

Also, if you cannot guarantee that the sub-query will return exactly one row, it is probably safer to use the SQL Server-specific and proprietary update format:

UPDATE f SET nav = n.nav
FROM camsfolio AS f
INNER JOIN camsnav AS n
ON f.schcode = n.schcode;

Answer 3

SQL Server doesn't use "generalised stored procedures" for this kind of thing. It's up to you to build your own SP, composed using an appropriate parameterized UPDATE statement.