How to execute stored procedure with table-valued parameter from SSMS

Question

When I right click a stored procedure, and select Execute Stored Procedure command, what should I pass to a table-valued parameter as value?

I am not talking about the SSMS "query" window. I am talking about the "Execute Procedure" dialog box that is shown, when you right-click on a SP.

Can I supply parameter values, directly into a SP via this screen?

Answer 1

This CAN be done. Let's assume, for example, that you have this table type defined:

CREATE TYPE dbo.ContactsList AS TABLE ( ContactID VARCHAR(25) NOT NULL ); GO

You want to execute a stored procedure that takes just one parameter of this type from within SSMS. To do so, choose Execute Stored Procedure from within SSMS, provide a JUNK value for the parameter value, and click OK. This will generate both the boilerplate code to execute the stored procedure AND give you an error message.

Next, modify the boiler plate to something like this, substituting @ContactIdsTableParameter (use your own variable name) for the JUNK value you previously provided:

DECLARE @ContactIdsTableParameter AS dbo.ContactsList

INSERT INTO @ContactIdsTableParameter (ContactID)
    SELECT 1
INSERT INTO @ContactIdsTableParameter (ContactID)
    SELECT 100002787

SELECT * FROM @ContactIdsTableParameter

DECLARE @return_value int

EXEC    @return_value = [dbo].[usp_MGOAssignmentsGet]
        @ContactIdsTable = @ContactIdsTableParameter

SELECT  'Return Value' = @return_value

GO

Finally, execute your version of the above with <F5> for example.

Answer 2

This is an oldie, but if anyone come across: You can utilize the execute sp dialog to render the execution script with null for the table valued parameter's value, and all the other parameters with their values. The script will not run for the first time. Then you can add declaration for the tvp and assign it as follow: (this is for a int list Type named IDsList)

DECLARE @return_value int
--this two rows for the tvp
DECLARE @p5 dbo.IDsList
insert into @p5 values(7020), (7026)

EXEC @return_value = [dbo].[stpGetSomting]
    @fromDate = N'2018-01-01',
    @toDate = N'2019-01-01',
    @companies = @p5

SELECT  'Return Value' = @return_value