how to make a conditional where clause in t-sql?

Farshad Valizade 381 Reputation points
2023-10-11T06:59:31.3833333+00:00

Hi everybody

I Have two kind of tables that each one has Draft and Original Report Types.

I have made a query to fetch data but I don't know how to get the right tables data from my parameters.

This is my code:

declare @ReportType tinyint = 1; 
declare @DraftOrginal tinyint = 1; 
select    
	*  
from    
Joint as j    
left join(     
			select        
					Fitup_DraftId,            
					Fitup_OrgId,               
					FitupID,        
					JointID      
			from        
			QC_Fitup as  ) as f on f.JointID = j.JointID  

 left join (     
				select        
					Weld_DraftId,              
					Weld_OrgId,           
				    FitupID      
				from        
				QC_Weld as w ) as w on w.FitupID = f.FitupID  

Now I want this kind of where clause :

where   if( @ReportType =1 AND @DraftOrg=1) then f.Fitup_DraftId =1000
where   if( @ReportType =1 AND @DraftOrg=2) then f.Fitup_OrgId =1001
where   if( @ReportType =2 AND @DraftOrg=1) then w.Weld_DraftId =1002
where   if( @ReportType =2 AND @DraftOrg=2) then w.Weld_OrgId =1004
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,895 questions
{count} votes

Accepted answer
  1. Viorel 112.9K Reputation points
    2023-10-11T11:23:28.99+00:00

    Check this condition:

    where ( @ReportType = 1 and @DraftOrg = 1 and f.Fitup_DraftId =1000 ) or
          ( @ReportType = 1 and @DraftOrg = 2 and f.Fitup_OrgId = 1001 ) or
          ( @ReportType = 2 and @DraftOrg = 1 and w.Weld_DraftId = 1002 ) or
          ( @ReportType = 2 and @DraftOrg = 2 and w.Weld_OrgId = 1004 )
    

1 additional answer

Sort by: Most helpful
  1. Olaf Helper 41,411 Reputation points
    2023-10-11T07:14:17.0933333+00:00

    The code is hard to read, the question to vague, no one "click" in databases and what will "WHERE ???" mean?

    Please post table design as DDL, some sample data as DML statement and the expected result.

    0 comments No comments