How do I pass a variable to a stored procedure to update a row in another database?

I need to get a value from one database for a customer and update another database with that value.

The procedure below works but I need to have it go through table2 and update every customer in table1 with a matching CustomerID. I hate to use the word loop through but as I said, I am very new to this and lost. I have watched videos, and tried to search with no luck. Can someone point me to a tutorial or tell me if I am trying to do something I shouldn’t be?

CREATE PROCEDURE dbo.bhshSample
as 
BEGIN;
    update table1 set
        ptall = (
            SELECT TOP (1) nsma1_ans
            from table2
            where nsma1_code = 'ptall'
            order by nsma1_tm
        )
    where CustomerID = '4'
End;

In php, I would loop and do a select distinct CustomerID from table 2

then do the update using the variable I set but I can’t seem to figure it out with stored procedure.

Answer

Use a correlated subquery, like this:

update table1 set
    ptall = (
        SELECT TOP (1) nsma1_ans
        from table2
        where nsma1_code = 'ptall'
        and CustomerID = table1.CustomerID
        order by nsma1_tm
    )

Advertisement

Answer