CODEWITHSUNDEEP
sql-serverstored-procedures
RichP
RichP

Reputation: 525

SQL Server Calling Stored Procedure Mutiple Times

My SQL guy is gone for a few days, I know enough to be dangerous. I have about 350 rows in an excel file but of course none of the cells are the primary key of the table I want to update.

I can write a select statement and join the necessary tables/fields to get the primary key I need. And I have a stored procedure to do the update I want.

How can I loop through the ~350 records and call that SP each time.

SP is very simple looks like this:

UPDATE
  tblAssessments
SET
  StatusCode = @statusCode
WHERE
  AssessmentID = @assessmentID

AssessmentID is the PK on the table I want to update. It happens to be a GUID. The excel file has a string with a cell named FacilityID that has the form 12345-123. It is unique as well. With a few joins I can get the 350 rows to link FacilityID to AssessmentID.

I'm guessing I'll import the excel file to a TEMP table then make joins a necessary to link FacilityID (string) to AssessmentID (guid). But open to any ideas if there is a better way.

Thanks!

EDIT:

I now have a table with 3-columns Looks like below. First column is my excel data middle column is what I've linked on one table in my DB the last column is the DB table in question that I want to update the other field.

FinSub  FacilityID  AssessmentID
563140-002  563140-002  4CD11CE1-7B8B-403C-B9B8-1D2A02A1B5B2
033737-001  033737-001  4FC73CD1-DAF2-405D-99F1-58F6A8262930
034490-001  034490-001  8641EF38-65F4-4496-A5E1-C998C5293D8D
036956-002  036956-002  31C5DEC7-09BE-426B-BADE-CA5D35AB3C1A

Error message when I run Joe C's code:

Msg 512, Level 16, State 1, Procedure trg_tblAssessments_UpdateFacilityInfo, Line 6 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

EDIT: I get the same error above if I run you updated code. If I run DISTINCT on either the first or third column of my temp table I get 344, which is the number of rows. I took a look at the trigger and it is nothing important to overall database or APP is supports. Any harm in disabling trigger then enabling when finished?

Upvotes: 0

Views: 76

Answers (1)

Joe C
Joe C

Reputation: 3993

You should use an update with a join. Once you have the ID attached, either in a temp table or a subquery, you can update all the rows with one statement and eliminate the need for the SP.

If you really want to use the SP approach you can use a cursor to loop through the 350 row table.

https://www.mssqltips.com/sqlservertip/1599/sql-server-cursor-example/

EDIT: Since you have a temp table this should work:

UPDATE T 
SET
  T.StatusCode = 9
From  tblAssessments T
Join (Select Distinct AssessmentID From TempTable) T2 
      On T2.AssessmentID = T.AssessmentID

Usually @statusCode would be a field on the joined table (T2), but if you want to put the same value on all matches you just need to declare and populate the variable before running the update.

Upvotes: 1

Related Questions