With table-valued parameters, available in SQL Server 2008, the update process is easy to implement and has little performance impact on the database.
Table-valued parameters can be passed as read-only input parameters.
I would upload the transaction IDs with the new date value in a temporary table, add a join between the transaction table and the temporary table, and update the transaction table with the new values from the temporary table.
Although it seemed relatively simple to update thousands of records with temporary tables, what if I had thousands of transactions that needed multiple field values updated?
Here is a better solution: -- if the procedure does not exist create a placeholder if not exists ( select * from sys.objects where name = N'p_My Proc' and type = N'P' ) begin exec('create procedure p_My Proc as RAISERROR (''My Proc not defined'', 16, 1);'); end go grant execute on p_My Proc to Some Role go -- update stored proc alter procedure p_My Proc as begin print 'go forth and do great things' end go NOTE: Thanks to Justin Wignall for bug-fix to the code above.
The statement that invokes the procedure so that you can obtain its value when the procedure returns.Using the drop create method there is obviously a small window of time when the stored procedure does not exist.And as we know from examples like the Seattle monorail crash (see notes at the end of this article), any system that is designed with a built-in flaw will eventually fail because of that flaw.SQL Server uses the sp_ prefix to designate system stored procedures.The name you choose may conflict with some future system procedure.