SQL

sql stored procedure with table parameter

CREATE PROC usp_PassTable
    @Keys KeyTable READONLY
AS
BEGIN
    SET NOCOUNT ON
    SELECT * FROM @Keys
END
GO

hot to pass a table valued parameter to a stored procedure in sql server

//To pass a valued parameter to a stored procedure you first need to create a 
//user defined table type.

CREATE TYPE TempTable AS TABLE
(
	INT SubjectID,
  	VARCHAR(50)
);

//Declare a parameter to pass on a stored procedure.
DECLARE @TempTable AS TempTable;
 
 //Insert into a declared value before passing it to a stored proc. 
INSERT INTO @TempTable
VALUES( 1, 'Math'),
	  ( 2, 'Science'),
      ( 3, 'Geometry');    

//Pass the variable to the stored procedure.
EXECUTE Usp_InsertLesson @TempTable;