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;
