• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

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
0
ziorinfo
Asked:
ziorinfo
1 Solution
 
Om PrakashCommented:
you need to pass the @PropertyID parameter in the SP. which can be done as:


EXECUTE AddPropertyProfit 5
OR
EXECUTE AddPropertyProfit @PropertyID =5
0
 
ziorinfoAuthor Commented:
I am already doing that the select command
 SELECT '1981' AS PropertyID
0
 
mimran18Commented:
Hi
plz try this

Drop table test
Go
Create table test
( [ID] int,
[name] nvarchar(50)
)
Go
Insert into test values (1,'test1')
Insert into test values (2,'test1')
Go
Drop table test2
Go
Create table test2
( [ID] int,
[name] nvarchar(50)
)
Go

Drop procedure testsp
Go
Create procedure testsp
@ID int,
@Name nvarchar(50)
as

Insert into [test2] values (@ID,@Name)

Go

declare @execstatementsbatch nvarchar(max)
select @execstatementsbatch = ''
SELECT @execstatementsbatch = @execstatementsbatch   + 'EXEC testsp ' + Convert(nvarchar(2),[ID]) +  ', '  +[Name] +  '; '
FROM [test] Where [ID] In (1,2)

exec(@execstatementsbatch)


Go
Select * from test2

Open in new window

0
 
kswathiCommented:
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.



0
 
dqmqCommented:
>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'

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now