mssql how to pass user defined table type to stored procedure from ssms Edit
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
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