Monday, October 31, 2016

How to use table types to execute a stored procedure in SSMS?

  1. Stored procedure expects  - table type - @TableType as an input parameter. In case you want to run the stored procedure in SSMS we can do the following -
  2. Find out the table definition of the table type. Best way to find it out is from code which calls the stored procedure.
  3. Define a variable of the TableType.
  4. Insert test values into the table type.
  5. Use the variable in the stored procedure.
  6. Example is as follows -

DECLARE @TableType TableType

INSERT INTO @TableType VALUES ('TEST','TEST')
EXEC [SPName]
  @Otherparameter= N'Test',
  @TableType= @TableType
 
GO

No comments: