best practive to pass multiple table to stored procedure in sql server
CREATE PROCEDURE MySproc
@PrimaryTable primary_tbltype READONLY,
@RelatedTable related_tbltype READONLY
AS
BEGIN
DECLARE @CurrentKey INT
DECLARE @FirstName VARCHAR(30)
DECLARE @LastName VARCHAR(30)
DECLARE @AccountTotal MONEY
DECLARE PersonCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT personkey, firstname, lastname FROM @PrimaryTable
OPEN PersonCursor
FETCH NEXT FROM PersonCursor INTO @CurrentKey, @FirstName, @LastName
WHILE @@FETCH_STATUS= 0 BEGIN
SELECT @AccountTotal = SUM(accountbalance) FROM @RelatedTable
WHERE fk_personkey = @CurrentKey
PRINT @FirstName + ' ' + @LastName + ' - account total: ' + CONVERT(VARCHAR(30), @AccountTotal)
FETCH NEXT FROM PersonCursor INTO @CurrentKey, @FirstName, @LastName
END
END;