How to pass table result as a parameter in stored procedure

---> Create user defined datatype,table type object

CREATE TYPE StateMaster AS TABLE
(
 StateCode VARCHAR(2),
 StateDescp VARCHAR(250)
)
GO

create table StateMst(
StateCode VARCHAR(2),
 StateDescp VARCHAR(250),
 todaydate datetime)
--> Use user defined type object as parameter in stored procedure

CREATE PROCEDURE TableValuedParameterExample_SP
(
@TmpTable StateMaster READONLY
)
AS
BEGIN
 INSERT INTO StateMst
  SELECT StateCode,StateDescp,getdate() FROM @TmpTable
END
GO



BEGIN
/* DECLARE VARIABLE OF TABLE USER DEFINED TYPE */
DECLARE @MyTable StateMaster

/* INSERT DATA INTO TABLE TYPE */
INSERT INTO @MyTable VALUES('11','AndhraPradesh')
INSERT INTO @MyTable VALUES('12','Assam')

/* EXECUTE STORED PROCEDURE */
EXEC TableValuedParameterExample_SP @MyTable
GO

--select * from StateMst

Difference Between the Delay validation and validate External meta data Properties in SSIS

DelayValidation --> validation of executable is delayed until runtime. For example, if you read from a table that has not yet been created, the package will crash when you start it, unless you set this property to true. The package waits with the validation of that executable until it has to run.

ValidateExternalMetadata --> validates a component during design time using external metadata. For example, if you are reading from an Excel file, the package will show an error during design time if the excel file does not exist. If you set this to False, the error should go away.

It is possible to have a package with an error because the excel file does not exist during design time, but if you run the package it will succeed when DelayValidation is set to True.

So, basically:
DelayValidation = runtime
ValidateExternalMetadat = design time 

we create same Local temp table in different sessions in SQL Sever ? If we can create how it will be saved in TempDB?

I have tried to create a Local Temp Table ( with the name of #temp) in 2 session in sql sever.
Tables has been created successfully in both the sessions with the same Local Temp Table(#temp) name.


After successfully created temp tables ,I have checked the TempDB to know the how tables will be saved. Please check below screen shot for your reference.




Tables has saved with unique extension number based on session.


Conclusion : We can created the same temp table in different sessions.
Please correct me if I am wrong.