Ed Pollack (8/6/2014)
Yes! You can definitely create an in memory table using a table type and pass it to a natively compiled stored proc. The only restriction is that the table type must be passed into the stored proc as READONLY. This is probably OK for whatever applications you have in mind, but the compiler will throw an error if you leave the keyword off of the parameter.
I didn't mean a "permanent" in-memory table, but a table variable, which is in-memory. The advantage of the table variable is that it is kept within scope, so multiple parallel applications can work without interfering with one another. Once the application completes, the table variable is gone. A permanent in-memory table needs to be cleaned up and managed by each instance of the application by an application instance ID of some sort.
Understood---and unfortunately, you can't do that. if you try to pass non-IMOLTP data to a memory-optimized table, you will get an error message that looks something like:
Msg 41323, Level 16, State 1, Procedure PROCNAME, Line 13
The table type 'dbo.TABLETYPENAME' is not a memory optimized table type and cannot be used in a natively compiled stored procedure.
As of now, the only flexibility you have is to create a standard stored proc that takes the table as a parameter and then uses that table and any other data you pass in to manipulate in-memory tables...which isn't particularly better than simply using your in-memory tables in standard stored procs with no additional modifications.
The limitations on mixing in-memory and non-in-memory data are fairly strict and all pointed in a single direction---ie you can pass in-memory data into non-in-memory objects without SQL Server complaining, but if you attempt to get any non-in-memory data into in-memory objects, you'll usually be out of luck.
Variables are OK, but table variables are handled in the same fashion as a temp table or standard table, since they do involve tempDB storage and that's a no-no for in-memory OLTP at the moment.