Use of SQL Server table value types

If you need to pass a table to a stored procedure, then the table-valued type is used

1. Create a table-valued type

CREATE TYPE [dbo]. [Storepreseparationamount] As TABLE ([Preseparationid] [int] not null,[preseparationamount] [decimal] (2) not null,[usercode] [varchar] (a) Not NU LL) GO

　　

2. Create a stored procedure

CREATE PROCSp_preseparationcommit (@spsastorepreseparationamount READONLY) asUPDATEPSSETPs.preseparationamount=Spsa.preseparationamount,ps.updater=Spsa.usercode,ps.updatetime=GETDATE() fromdbo.wms_preseparation PSINNER JOIN @spsaSpsa onPs.preseparationid=Spsa.preseparationid

3.ado.net using table-valued types

public bool Updatepreseparationamount (DataTable dtstorepreseparationamount)        {            string strspname = @ "sp_ Preseparationcommit ";            sqlparameter[] Pars =                    {                        new SqlParameter ("@spsa", sqldbtype.structured),                    };            Pars[0]. Value = Dtstorepreseparationamount;            Pars[0]. TypeName = "Storepreseparationamount";            int num = Dalbase.executenonquery (commandtype.storedprocedure, strspname, pars);            if (num > 0)            {                return true;            }            else            {                return false;            }        }

Note: Table creation

DataTable Dtstorepreseparationamount = new DataTable ("Dtstorepreseparationamount");                        DTSTOREPRESEPARATIONAMOUNT.COLUMNS.ADD ("Preseparationid", Type.GetType ("System.Int32"));                        DTSTOREPRESEPARATIONAMOUNT.COLUMNS.ADD ("Preseparationamount", Type.GetType ("System.Decimal"));                        DTSTOREPRESEPARATIONAMOUNT.COLUMNS.ADD ("Usercode", Type.GetType ("System.String"));  DataRow drnew = Dtstorepreseparationamount.newrow ();                                drnew["Preseparationid"] = Npreseparationid;                                drnew["Preseparationamount"] = Dpreseparationamount;                                drnew["Usercode"] = login. UserName;                                DTSTOREPRESEPARATIONAMOUNT.ROWS.ADD (drnew);

　　

Use of SQL Server table value types