-3
Select OpportunityId 
from opportunity AS c 
    left JOIN (
        select a.opportunitynameid 
        from opportunity o
            JOIN ApprovalDocument a ON a.opportunitynameid=o.OpportunityId
    ) AS b ON c.OpportunityId=b.opportunitynameid
Where b.opportunitynameid IS NULL and statecode=0
iberbeu
  • 15,295
  • 5
  • 27
  • 48
Debleena De
  • 1
  • 3
  • 1
    What part of pasting that into a stored procedure is troubling you? – TZHX Mar 16 '16 at 13:08
  • 1
    I imagine the syntax and examples of the `CREATE PROCEDURE` command is something that can be easily found on Google. Where specifically are you stuck? What have you tried that isn't working? – David Mar 16 '16 at 13:13
  • Possible duplicate of [convert my sql query to queryexpression or fetchxml in crm](http://stackoverflow.com/questions/35955040/convert-my-sql-query-to-queryexpression-or-fetchxml-in-crm) – f_puras Mar 16 '16 at 14:19

2 Answers2

0
Create Procedure mySP_GetOpportunity
@statuscode int = 0
As
Select OpportunityId 
from opportunity AS c left JOIN 
    ( select a.opportunitynameid from opportunity o JOIN ApprovalDocument a ON a.opportunitynameid=o.OpportunityId ) AS b 
    ON c.OpportunityId=b.opportunitynameid 
Where b.opportunitynameid IS NULL 
and statecode=@statuscode
Jevl
  • 924
  • 9
  • 8
0

Wrap the sql query between CREATE PROCEDURE Proc_Name AS "query goes here" GO for more detail refer this

NishanthSpShetty
  • 661
  • 5
  • 14