mssql how to filter from select where in list only if the list is not null Edit

Murugan Andezuthu Dharmaratnam | 15 October 2020 | 156

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.