ahmed elbarbary

ahmed elbarbary

  • 1.2k
  • 667
  • 25.9k

How to create dynamic sql based on optional values from temp

Jan 24 2020 3:58 AM
I have #TempMaster temp table have 3 fields
with optional values on 3 fields SourceGeneralTypeID AND StatusGeneralTypeID AND DailyLogId
meaning
may be SourceGeneralTypeID have values and other 2 fields not have value
may be StatusGeneralTypeID have values and other 2 fields not have value
may be DailyLogId have values and other 2 fields not have value
so my problem
How to write on statement after join on ? = ? where ?
problem how to write On ? = ? where ???????????????
and what i write on where
based on details above
 
  1. what i have tried  
  2.   
  3. select M.MasterDataID,M.TrackingNumber,M.StatusDate  
  4.  from #TempMaster tmp  
  5. INNER join [MasterData] M on ???=?????  
  6. where ??????  
  1. CREATE TABLE [MasterData](  
  2.     [MasterDataID] [int] IDENTITY(1,1) NOT NULL,  
  3.     [SourceGeneralTypeID] [intNULL,  
  4.     [StatusGeneralTypeID] [intNULL,  
  5.     [DailyLogId] [intNULL,  
  6.  CONSTRAINT [PK_MasterData] PRIMARY KEY CLUSTERED   
  7. (  
  8.     [MasterDataID] ASC  
  9. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,   
  10.   
  11. IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,   
  12.   
  13. ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  14. ON [PRIMARY]  
  15.   
  16. create table #TempMaster(  
  17.   
  18. SourceGeneralTypeID int,  
  19. StatusGeneralTypeID int,  
  20. DailyLogId  int  
  21. )  
  22. insert into #TempMaster  
  23.   
  24. (SourceGeneralTypeID,StatusGeneralTypeID,DailyLogId)  
  25. values  
  26. (Null,10,20),  
  27. (2,Null,30),  
  28. (2,30,Null)  
  29.   
  30.   
  31.   
  32.   
  33. insert into [MasterData]   
  34. ([SourceGeneralTypeID],[StatusGeneralTypeID],[DailyLogId])  
  35. values  
  36. (2,30,20),  
  37. (2,30,30),  
  38. (2,30,10)  
 

Answers (5)