title: Using a stored procedure on each row in a table
body: <p>I have a table with some accounts and vendors. I need to run a stored procedure on each row to add them to other tables.</p>
  
  <p>Everything im reading says to try not use loops, which makes sense. But can anyone tell me how I would do this without them?</p>
  
  <p>Sample table </p>
  
  <pre><code> AccountNum | VendorName
  ------------------------
  1234       | Vendor1
  2345       | Vendor2
  3456       | Vendor1
  4567       | Vendor3
  </code></pre>
  
  <p>Then my Stored Procedure is below. So i need to pass in the account numbe rand vendor from each row into the stored procedure below. (Dont worry about the premise number and user, they can be N/A for all). So is there a way to do this without looping through each row? Im using SQL Server Management Studio 2014. Cheers</p>
  
  <pre><code>USE [PEEL_ICEM]
  GO
  /****** Object:  StoredProcedure [dbo].[AddAccountNumber]    Script Date:     2/11/2016 3:59:16 PM ******/
  SET ANSI_NULLS ON
  GO 
  SET QUOTED_IDENTIFIER ON
  GO
  
  
  ALTER PROCEDURE [dbo].[AddAccountNumber]
  @AccountNumber nvarchar(150),
  @UserName nvarchar(100),    
  @PremiseNumber nvarchar(150) = Null,
  @VendorName nvarchar (100)
  AS
  BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;
  
  SET XACT_ABORT on
  
  IF @VendorName IS NOT NULL
  BEGIN
      DECLARE @CustomerID int
      DECLARE @LIPharseID int
      DECLARE @ExportLabelID int
      DECLARE @CustVenID  int
      DECLARE @AccountNo varchar(20)
      --Get the Customer ID with the user name
      SELECT @CustomerID = [Customers].[CustomerID]
      FROM [CustomerUsers]
      INNER JOIN [Customers] ON [Customers].[CustomerID] = [CustomerUsers].CustomerID
      INNER JOIN Users ON Users.UserID = [CustomerUsers].UserID WHERE Users.Username = @UserName
      print  @CustomerID 
      --Get the VendorID from the Vendor name, test
      DECLARE @VendorID int
      SELECT @VendorID = [VendorID]  FROM [Vendors] WHERE VendorName = @VendorName
      print  @VendorID    
      BEGIN TRAN
          --Check if the customer vendor combination already exists       
          SELECT @CustVenID = [CustomerVendorID] FROM [PEEL_ICEM].[dbo].[CustomerVendors] WHERE [CustomerID] = @CustomerID AND [VendorID] = @VendorID
          print  @CustVenID
          Select @AccountNo = [AccountNumber] From [PEEL_ICEM].[dbo].[AccountVendor] WHERE [CustomerVendorID] = @CustVenID AND [AccountNumber] = @AccountNumber
          print  @AccountNo
          IF @CustVenID IS NULL  AND @VendorID &lt;&gt; '' AND @CustomerID &lt;&gt; '' 
              BEGIN                
                  INSERT INTO [CustomerVendors]
                         ([CustomerID]
                         ,[VendorID])
                   VALUES
                         (@CustomerID,@VendorID)      
                  SELECT @CustVenID = SCOPE_IDENTITY()                
              END 
          print  @CustVenID
          if @AccountNo is Null
              begin
                  IF @CustVenID IS NOT NULL AND @AccountNumber &lt;&gt; ''
                      BEGIN   
                          INSERT INTO [AccountVendor]
                          ([AccountNumber]
                          ,[CustomerVendorID]
                          ,[PremiseNumber])           
                          VALUES
                          (@AccountNumber,@CustVenID, @PremiseNumber)
                      END
                  ELSE
                      BEGIN
                      SELECT 'ERROR' as Status
                  END     
              End
  
      COMMIT TRAN 
  
      SELECT [VendorName],[AccountNumber],[PremiseNumber]     
      FROM [CustomerVendors]
      LEFT JOIN [AccountVendor] ON [CustomerVendors].[CustomerVendorID] = [AccountVendor].[CustomerVendorID]
      LEFT JOIN [Vendors] ON [Vendors].[VendorID] = [CustomerVendors].[VendorID]
      WHERE [CustomerVendors].CustomerID = @CustomerID
  END
  </code></pre>
  
  <p>END </p>