asked on

how to pass parameters to an SP using select command

I have a SP and I am trying to call it and trying to send the parameter using a select command

right now i am getting this error when i run the below sql statements

Msg 201, Level 16, State 4, Procedure AddPropertyProfit, Line 0
Procedure or function 'AddPropertyProfit' expects parameter '@PropertyID', which was not supplied.

(1 row(s) affected)

could any one help me out here pls

DECLARE @ID INT

EXECUTE AddPropertyProfit
SELECT '1981' AS PropertyID,
      'Listing' AS [PropertyProfitType]
      ,Active
      ,AcqPurchasePrice
      ,AcqPurchaseClosingDate
      ,AcqRealPurchasePrice
      ,AcqCommissionPercentage
      ,AcqCommissionTotal
      ,AcqCommissionTo
      ,AcqClosingCosts
      ,AcqPropertyTax
      ,AcqHudEarlyClosingBonus
      ,AcqPurchaseCreditsAndRefunds
      ,RehabSupervisionFeeAmount
      ,RehabSupervisionFeeTo
      ,RehabCosts
      ,RehabNewLoan
      ,RehabNewLoanAmount
      ,RehabNewLoanDebtService
      ,RehabNewLoanLender
      ,RehabLoanInterestPayments
      ,RehabLoanFees
      ,RehabPropertyTaxes
      ,RehabPropertyTaxesPercentageOfMV
      ,RehabInsurance
      ,RehabInsurancePercentageOfMV
      ,RehabUtilities
      ,RehabCashForKeys
      ,RehabCreditsAndRefunds
      ,RehabMisc
      ,RehabMiscDescription
      ,CloseCommissionPercentage
      ,CloseCommissionTotalAmount
      ,CloseSellerCosts
      ,CloseSellerPaidBuyerCostsPercentage
      ,CloseSellerPaidBuyerCosts
      ,ClosePestInspection
      ,CloseInspections
      ,CloseInspectionDescription
      ,ClosePropertyTaxes
      ,CloseClosingCredits
      ,CloseManagementFeePercentage
      ,CloseManagementFeeAmount
      ,ClosingBookkeeping
      ,CloseMisc
      ,CloseMiscDescription
      ,SaleClosingDate
      ,FinalSalesPrice
      ,UpdatedOn
      ,UpdatedBy
      ,CreatedOn
      ,OccupiedVacantStatusID
      ,WalkThroughEstimate
      ,ApprovedBudget
      ,EvaluationRehab
      ,EvaluationRehabOther
      ,AcqDeedRecordingFee
      ,RehabLegalFee
      ,RehabHOAExpense
      ,RehabMaintenanceFees
      ,RehabPestRepairs
      ,RehabVandalismRepairs
      ,@ID output
      FROM PropertyProfit
      WHERE PropertyProfitID = 5

Om Prakash

you need to pass the @PropertyID parameter in the SP. which can be done as:

EXECUTE AddPropertyProfit 5
OR
EXECUTE AddPropertyProfit @PropertyID =5

Mahesh Yadav

ASKER

I am already doing that the select command
SELECT '1981' AS PropertyID

ASKER CERTIFIED SOLUTION

mimran18

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kswathi

First of all you need to declare it as parameter if not declared already in the sp if 1981 is static then

just Set @PropertyID =' 1981'

EXECUTE AddPropertyProfit @PropertyID

you need to set the parameter before calling the sp.

dqmq

>I am already doing that the select command
SELECT '1981' AS PropertyID

No, you are not. In the following sequence:

EXECUTE AddPropertyProfit
SELECT '1981' AS PropertyID,

EXECUTE is one statement and SELECT is another statement. '1981' does not get passed to the procedure. You need to do like this:

EXECUTE AddPropertyProfit '1981'