s

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

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

author
Murugan Andezuthu Dharmaratnam | calendar 15 October 2020 | 2443

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