mssql how to pass user defined table type to stored procedure from ssms Edit

Murugan Andezuthu Dharmaratnam | 15 October 2020 | 211

I have a stored procedure which accepts parameters which are of type user defined table type. I am adding the script to create the user defined table type as well as the stored procedure and ssms script to call the stored procedure.

User defined table type

CREATE TYPE [dbo].[type_id] AS TABLE(
	[Id] [uniqueidentifier] NULL
)
GO
           
                    

Stored Procedure

CREATE PROCEDURE [dbo].[sp_recipe_search]
	@SetupCuisineId [dbo].[type_id] NULL readonly
AS
BEGIN
BEGIN TRANSACTION

	select * from Recipe where 1=1
	AND Recipe.SetupCuisineId in (select Id from @SetupCuisineId)
COMMIT
END
GO              
                    

How to call from SSMS

declare @temp as [dbo].[type_id]
insert into @temp(Id) values('5B4E5A84-8706-4231-B010-51ED5748C5B3')
exec sp_recipe_search @temp