Pass Table Variable as a Input parameter to Stored Procedure

What about other dialects?

Yes... that is precisely what my fix does...

Thank you Peres, Finally I got it work.
I followed the link https://stackoverflow.com/questions/3701364/nhibernate-sqldbtype-structured

in Sql2008Structured Class , at the below method I changed the statement s.Parameters[index].TypeName = "MyTablevariableType";
public void NullSafeSet(IDbCommand st, object value, int index, NHibernate.Engine.ISessionImplementor session) {
var s = st as SqlCommand;
if (s != null) {
s.Parameters[index].SqlDbType = SqlDbType.Structured;
s.Parameters[index].TypeName = "IntTable"; -----This is my TablevariableType.
s.Parameters[index].Value = value;
}
else {
throw new NotImplementedException();
}
Then It works fine.
Thanks for your help and quick reply.

But did you clone NHibernate, got my pull request, compiled NH and tested with this version?

Hi Peres,

I tried with
var Mytable = new DataTable("dbo.TableType");
Mytable.Columns.Add("CategoryID", typeof(int));
Mytable.Columns.Add("CategoryName", typeof(string));
Mytable.Rows.Add(1, "TestSatish");

var result = session.CreateSQLQuery("EXEC usp_TestSatiUpdateCatgorys :t").SetParameter("t", Mytable).List();. But I am getting "Operand type clash: varbinary is incompatible with TestSatishTableType1 error.

Then i tried with other option which is var result = session.CreateSQLQuery("EXEC dbo.TableProcedure :t").SetParameter("t", Mytable, NHibernateUtil.Structured("dbo.TableType")).List(); But I am getting Compilation error NHibernateUtil class does not contains a definition for Structured.

When I check at SQl Server Side Stored procedure and Table variable are working fine. Can you please let me know where I am doing wrong.

At Database side my table variable is below
'CREATE TYPE [dbo].[TestSatishTableType1] AS TABLE(
[CategoryID] [int] NULL,
[CategoryName] [nvarchar](max) NULL
)
GO'

and my Stored Procedure is below

ALTER PROCEDURE [dbo].[usp_TestSatiUpdateCatgorys]
@TestSatish as TestSatishTableType1 READONLY
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Test_SatishTable as TestSatishTableType1
insert into dbo.TestSatishCategoryTable1 (CATID,CATNAME) select nc.CategoryID, nc.CategoryName FROM @TestSatish AS nc;
END
GO

Can you please let me know where I am doing mistake.

Thanks in advance.