Pass a parameter in a stored procedure

• Alice Sanders

  SSC Journeyman

  Points: 77

  More actions

  April 7, 2006 at 8:27 am

  #114264

  I need to create a stored procedure that imports Application IDs from another sp instead of me specifying the ids in the execute statement.  I created 1 sp to identify the application ids that I need. 

   

  create PROCEDURE spNewValue as

  SELECT     APPLICATION.APPLICATION_ID as AppID

  FROM         SETTLEMENT_AMENDMENT LEFT OUTER JOIN

                        APPLICATION ON SETTLEMENT_AMENDMENT.APPLICATION_ID = APPLICATION.APPLICATION_ID

  GO

   

  Then, I have a second sp where I want all the App IDs from the 1st sp to be used as a AppID parameter so I don't have to create an execute statement because there are over 3,500 project ids.

   

   

  create PROCEDURE [dbo].[spUpdateSettAmtTotals]

   

  AS

   

  DECLARE     @AppID int

  EXECUTE spNewValue @AppID OUTPUT

  EXECUTE @AppID=FundingRequest.dbo.ApplicationID.AppID

  select AppID=@AppID

  GO

  .........  There is more to this sp, however, it is long so I abbreviated it.

   

  This is the error message I receive when I execute the sp: "Procedure spNewValue has no parameters and arguments were supplied".

   

  Can someone please give me some guidance on what I am doing wrong?   I would greatly appreciate it!

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  April 7, 2006 at 8:44 am

  #631521

  You cannot pass record sets around into variables like that. Sql server does not support Arrays.

  Give this a read it has all you need.

  http://www.sommarskog.se/share_data.html