Is it possible to run stored procedure to set value in update statement - TSQL

Question

I'm wondering if its possible to execute a stored procedure in an update statement in TSQL.

I want to execute a stored procedure that will set the CategoryID for the number table, passing in the number from the row the update statement is currently on.

So something like:

UPDATE [TelephoneNumberManagement].[dbo].[Number]
SET [CategoryID] = exec goldennumbers2 [Number];

Answer 1

No.

You could do this if it were a function:

UPDATE [TelephoneNumberManagement].[dbo].[Number]
SET [CategoryID] = goldennumbers2([Number]);

Just keep in mind that a function can't have side-effects. If you're trying to run a bunch of DML statements in that procedure you should:

• A) Use a trigger, if you have dependencies in other tables that need to be kept in-sync with your Number table. Even so you might want to...
• B) Rethink your design. This feels like trying to mix set-based and iterative programming practices. There's almost certainly a more pure solution.

Answer 2

Not really, there are some options like user-defined functions. Triggers might be able to do what you want, depending upon what you're trying to do and why.

What exactly does your goldennumbers2 procedure do?