Monday, 9 November 2015

how to create and use table variable

create User Defined Table type in Programmability in sql
my table type is 


1)    CREATE TYPE [dbo].[sitenamelist] AS TABLE(
[siteid] [int] NULL,
[Userid] [int] NULL,
[isdeleted] [bit] NULL,
[createdby] [int] NULL,
[modifiedby] [int] NULL
)

2) calll table type in storedprocedure
 @sitelist as sitenamelist READONLY

INSERT INTO MDTM_AdminSiteLink (SiteID,UserId,IsDeleted,[DateCreated], [CreatedBy]) select SiteID,Userid,isdeleted,GETDATE(),CreatedBy from sitenamelist


3)pass parameter in data layer
 comd.Parameters.AddWithValue("@sitelist", sitelist);

create get set DataTable Type in business entity
private DataTable _sitelist;
        public DataTable sitelist
        {
            get { return _sitelist; }
            set { _sitelist = value; }
        }

No comments:

Post a Comment