mssql how to filter from select where in list only if the list is not null Edit
What I am trying to do is, I have a list of Ids I am passing to my stored procedure. If there passed Id's have value filter it, if there are no values return everything. This is how I was able to do it.
Sample Stored Procedure
CREATE PROCEDURE [dbo].[sp_recipe_search] @SetupCuisineId [dbo].[type_id] NULL readonly, AS BEGIN BEGIN TRANSACTION select Id into #SetupCuisineId from @SetupCuisineId IF NOT EXISTS(select * from #SetupCuisineId) insert into #SetupCuisineId select SetupCuisineId as Id from Recipe select * from Recipe where 1=1 AND Recipe.SetupCuisineId in (select Id from #SetupCuisineId) COMMIT END
check this article to learn about how to pass ids to stored procedure using user-defined table type . I tried to google for a solution but was not successful. This is the hack I was able to write to filter only if the list of ids is not empty. Here in this code, I am copying all the id's to a temporary table and if the ids or the temporary table is empty I am copying all the ids from the main table to the temporary table.