CODEWITHSUNDEEP
sqlsql-server
Shahzad Ahamad
Shahzad Ahamad

Reputation: 829

How to filter data based on different values of a column in sql server

I am stuck at a point. I want to select based on the column entitytype if entitytype value is Booking or JOb then it will filter on its basis but if it is null or empty string('') then i want it to return all the rows containing jobs and bookings

create proc spproc
 @entityType varchar(50)
 as 
 begin
 SELECT TOP 1000 [Id]
      ,[EntityId]
      ,[EntityType]
      ,[TenantId]
  FROM [FutureTrakProd].[dbo].[Activities]
where TenantId=1 and EntityType= case @EntityType when 'BOOKING' then 'BOOKING'                                   
                                    when 'JOB' then 'JOB'                                   
                                    END  
end

Any help would be appreciable

Thankyou

Upvotes: 0

Views: 62

Answers (4)

Ullas
Ullas

Reputation: 11566

You can execute a dynamic sql query.

Query

create proc spproc
 @entityType varchar(50)
 as 
 begin

 declare @sql as nvarchar(max);
 declare @condition as nvarchar(2000);
 select = case when @entityType is not null then ' and [EntityType] = @entityType;' else ';' end;
 select @sql = 'SELECT TOP 1000 [Id], [EntityId], [EntityType], [TenantId] FROM [FutureTrakProd].[dbo].[Activities] where TenantId = 1 ';

 exec sp_executesql @sql, 
 N'@entityType nvarchar(1000)',
 @entityType = @entityType

end

Upvotes: 0

Thom A
Thom A

Reputation: 96038

You don't need a CASE expression for this, you just need an OR. The following should put you on the right path:

WHERE TenantId=1
  AND (EntityType = @EntityType OR @EntityType IS NULL)

Also, note it would also be wise to declare your parameter as NULLable:

CREATE PROC spproc @entityType varchar(50) = NULL

This means that someone can simply exclude the paramter, value than having to pass NULL (thus EXEc spproc; would work).

Finally, if you're going to have lots of NULLable parameters, then you're looking at a "catch-all" query; the solution would be different if that is the case. "Catch-all" queries can be notoriously slow.

Upvotes: 3

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You don't need to use case expression you can do :

SELECT TOP 1000 [Id], [EntityId], [EntityType], [TenantId]
from [FutureTrakProd].[dbo].[Activities]
WHERE TenantId = 1 AND
      (@EntityType IS NULL OR EntityType = @EntityType)
ORDER BY id; -- whatever order you want (asc/desc)

For your query procedure you need to state explicit ORDER BY clause otherwise TOP 1000 will give random Ids.

Upvotes: 3

Cetin Basoz
Cetin Basoz

Reputation: 23867

create proc spproc
 @entityType varchar(50)
 as 
 begin
 SELECT TOP 1000 [Id]
      ,[EntityId]
      ,[EntityType]
      ,[TenantId]
  FROM [FutureTrakProd].[dbo].[Activities]
where TenantId=1 and (@EntityType is null OR EntityType= @EntityType)
end

Upvotes: 4

Related Questions