How can a table be passed as parameter to another procedure?

To pass a table from one procedure to another first we need to create a user-defined table type. In which we will define the table.

CREATE type [dbo].[EmployType] as table

(

            [EmployID] [int] NULL,

            [EmployName] [varchar](30) NULL,

            [EmploySalary] [int] NULL

)

Then create a permanent table in which data will be inserted in the procedure

CREATE TABLE [dbo].[Employ]

(

            [EmployID] [int] NULL,

            [EmployName] [varchar](30) NULL,

            [EmploySalary] [int] NULL

)

Now create a procedure with passing the table as the parameter. Note that the table passed will remain as readonly type

CREATE PROCEDURE dbo.EmployDetailProcedure

 ( @EmployDetail dbo.EmployType READONLY )

AS

BEGIN

   SET NOCOUNT ON

   INSERT INTO dbo.[Employ] ([EmployID], [EmployName], [EmploySalary])

   SELECT [EmployID], [EmployName], [EmploySalary] FROM @EmployDetail

END

Now executing the procedure via passing the table to the procedure as table

DECLARE @EmployVariable AS EmployType

INSERT INTO @EmployVariable([EmployID], [EmployName], [EmploySalary]) VALUES(1,'Alex','200000')

EXEC dbo.EmployDetailProcedure @EmployVariable