s

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

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

author
Murugan Andezuthu Dharmaratnam | calendar 15 October 2020 | 2192

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.