August 15, 2017 at 3:05 pm
I have a Stored Procedure that updates a row in the Database.
The SP does require 5 Parameters.
When modifying multiple rows, I need to make the change in a single transaction.
I used to create a huge SQL commands including multiple time the same SP.
Since the limit of Parameters for a 2100, If I have 420 rows to update, I won't be able to execute the SQL command.
it looked like :
BEGIN TRAN
exec [SpName] @TableNo0, @PK_0, @Param1_0, @Param2_0, @Param3_0
exec [SpName] @TableNo1, @PK_1, @Param1_1, @Param2_1, @Param3_1
exec [SpName] @TableNo2, @PK_2, @Param1_2, @Param2_2, @Param3_2
exec [SpName] @TableNo3, @PK_3, @Param1_3, @Param2_3, @Param3_3
exec [SpName] @TableNo4, @PK_4, @Param1_4, @Param2_4, @Param3_4
exec [SpName] @TableNo5, @PK_5, @Param1_5, @Param2_5, @Param3_5
exec [SpName] @TableNo6, @PK_6, @Param1_6, @Param2_6, @Param3_6
...
COMMIT
I was wondering if there was a way I could use a temp table(or any other mean), update the row as I want and then feed them one by one to the Stored Proc?
I'm using SQL Server 2008 R2 and above.(It needs to work on 2008 R2)
Thanks a lot, feel free to ask questions if I wasn't clear enough.
August 15, 2017 at 3:21 pm
August 15, 2017 at 3:45 pm
The row that are updated by the SP are coming from a view. The view is compose of multiple table have the same layout. (Partitioning done the wrong way)
The StoredProc uses one of the parameters to identify in what Table to update and an other for the primaryKey of the row to updateexec [SpName] @TableNo0, @PrimaryKey0, @Param1_0, @Param2_0, @Param3_0
The Database that is the source of the rows to update is also the destination.
August 15, 2017 at 4:13 pm
Why not update directly from the view:
UPDATE mt
SET col1 = mv.col1
, col2 = mv.col2
FROM myView mv
JOIN myTable mt ON mv.PKID=mt.PKID;
August 15, 2017 at 4:24 pm
Do all of your parameters need to be unique? That is, is could you reduce the number of parameters by reusing some?
If not, could update the parameters as you go? That way you'd only need 5 parameters.
If you have to manually enter each parameter, it'll get tiring on your fingers and increase the risk of "fat fingering" something, but the same problem exists with having 2100 parameters. If you are pulling them from some other source, you could likely populate the 5 parameters inside a cursor, no?
As much as everyone hates cursors, they do sometimes serve a purpose...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply