Iterating between the rows of a Table valued parameter

Question

CREATE TABLE [Target]
     (
     [ID] [int] NOT NULL PRIMARY KEY IDENTITY,
     [FirstName] [varchar](100)NOT NULL,
     [LastName] [varchar](100)NOT NULL,
     [Email] [varchar](200) NOT NULL
     )
    CREATE TYPE [TargetUDT] AS TABLE
     (
     [FirstName] [varchar](100)NOT NULL,
     [LastName] [varchar](100)NOT NULL,
     [Email] [varchar](200) NOT NULL
     )
     CREATE PROCEDURE AddToTarget(@TargetUDT TargetUDT READONLY)
     AS
     BEGIN
           INSERT INTO [Target]
           SELECT * FROM @TargetUDT
     END

This is the answer of 1 of the posts i found in stackoverflow.

consider that i am having 10 rows with the type table "TargetUDT" Does BEGIN-END act as a loop? i mean does insert works row by row? I need to do certain calculations for each row. The calculations may vary row by row. So can i place them in between the BEGIN AND END?

i will give my code below

foreach (MdlGeneralLedger objJournalDetail in objJouranlDetails)
                {
                    MdlGeneralLedger objTransDetails = new MdlGeneralLedger();
                    objTransDetails.intGLAccountId = objJournalDetail.intGLAccountId;
                    objTransDetails.dateGLTransDate = objJournalEntry.dtjournalDate;
                    if (objJournalDetail.decDebitAmount != 0)
                    {
                        objTransDetails.decGLTransAmount = objJournalDetail.decDebitAmount;
                        objTransDetails.boolGLIsDebit = true;
                    }
                    else
                    {
                        objTransDetails.decGLTransAmount = objJournalDetail.decCreditAmount;
                        objTransDetails.boolGLIsDebit = false;
                    }
                    objTransDetails.guidCompanybranchId = objJournalEntry.guidCompanybranchId;
                    objTransDetails.strGLTransRemark = "Journal Entry";
                    objTransDetails.boolGLIsActive = true;
                    objTransDetails.strTransRefNumber = objJournalEntry.strJournalReference;
                    objTransDetails.intFiscalYearId = objJournalEntry.intFiscalYearId;
                    objTransDetails.inttblReferenceId = (int)TblReference.JournalEntry;
                    objTransDetails.strPerson = "Vidya";
                    MdlGeneralLedger objGldetails_blnc = new MdlGeneralLedger();
                    objGldetails_blnc.intGLAccountId = objJournalDetail.intGLAccountId;
                    if (objTransDetails.boolGLIsDebit)
                        objGldetails_blnc.decGLTransAmount = objTransDetails.decGLTransAmount;
                    else
                        objGldetails_blnc.decGLTransAmount = -objTransDetails.decGLTransAmount;
                    objJournalDetail.decGLTransAmount = objTransDetails.decGLTransAmount;
                    temp.Rows.Add(count++,_intJouranlId, objTransDetails.strTransRefNumber, objTransDetails.dateGLTransDate, objGldetails_blnc.decGLTransAmount, objTransDetails.strGLTransRemark, objTransDetails.guidCompanybranchId, objTransDetails.intGLAccountId, objTransDetails.intFiscalYearId, objTransDetails.boolGLIsDebit, objTransDetails.strPerson, objTransDetails.inttblReferenceId, objJournalDetail.decGLTransAmount, objJournalDetail.strJournalmemo);
                }

public static DataTable tbl_mdl()
    {

        DataTable temp = new DataTable();
        //objTransDetails
        temp.Columns.Add("slno", typeof(int));
        temp.Columns.Add("Journal_Master_Id", typeof(int));
        temp.Columns.Add("strTransRefNumber", typeof(string));
        temp.Columns.Add("dateGLTransDate", typeof(DateTime));
        temp.Columns.Add("decGLTransAmount", typeof(decimal));
        temp.Columns.Add("strGLTransRemark", typeof(string));
        temp.Columns.Add("guidCompanybranchId", typeof(Guid));
        temp.Columns.Add("intGLAccountId", typeof(int));
        temp.Columns.Add("intFiscalYearId", typeof(int));            
        temp.Columns.Add("boolGLIsDebit", typeof(bool));            
        temp.Columns.Add("strPerson", typeof(string));
        temp.Columns.Add("inttblReferenceId", typeof(int));
        //objGldetails_blnc
        temp.Columns.Add("decGLTransAmount2", typeof(decimal));
        //objJournalDetail 
        temp.Columns.Add("strJournalmemo", typeof(string));
        return temp;

    }

settingdetails.cs

public int InsertJournalEntryCheck(DataTable temp)
        {
            return privateInsertJournalEntryCheck(temp);
        }
        public int privateInsertJournalEntryCheck(DataTable temp)
        {
            SqlParameter objsqlparameter = new SqlParameter("@LIST", SqlDbType.Structured);
            objsqlparameter.Value = temp;
            if (base.Transaction != null)
            {
                return (int)_helper.ExecuteScalar(base.Transaction, CommandType.StoredProcedure, "usp_insertGl_transtemp2", objsqlparameter);
            }
            else
            {
                return (int)_helper.ExecuteScalar(base.ConnectionString, CommandType.StoredProcedure, "usp_insertGl_transtemp2", objsqlparameter);
            }
        }
                if (dalHelper.GetDAL_SettingDetails(true).InsertJournalEntryCheck(temp) == 0)
                {
                    dalHelper.EndConnectionAndRollBackTransaction();
                    return 0;
                }

now my sp:

    CREATE TYPE [dbo].[JNLOBJLIST] AS TABLE(
[slno] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Journal_Master_Id] [int] NULL,
[strTransRefNumber] [varchar](50) NULL,
[dateGLTransDate] [date] NULL,
[decGLTransAmount] [decimal](18, 4) NULL,
[strGLTransRemark] [varchar](50) NULL,
[guidCompanybranchId] [uniqueidentifier] NULL,
[intGLAccountId] [int] NULL,
[intFiscalYearId] [int] NULL,
[boolGLIsDebit] [binary](1) NULL,
[strPerson] [varchar](50) NULL,
[inttblReferenceId] [int] NULL,
[decGLTransAmount2] [decimal](18, 4) NULL,
[strJournalmemo] [varchar](50) NULL)

sp:

CREATE PROCEDURE [dbo].[usp_insertGl_transtemp2] 
@LIST [dbo].JNLOBJLIST READONLY

AS
DECLARE @id int

BEGIN

INSERT INTO tbl_GL_Trans_Detailstemp
                    ( trans_ref_number
                    , GL_trans_date
                    , GL_trans_amount
                    , GL_trans_remark
                    , company_branch_id
                    , GL_Account_id
                    , fiscal_year_id
                    , IsDebit
                    , Person
                    , tbl_reference_Id)

SELECT              strTransRefNumber
                    , dateGLTransDate
                    , decGLTransAmount
                    , strGLTransRemark
                    , guidCompanybranchId
                    , intGLAccountId
                    , intFiscalYearId
                    , boolGLIsDebit
                    , strPerson
                    , inttblReferenceId FROM @LIST

SET @id = (SELECT MAX(GL_trans_id)
FROM   tbl_GL_Trans_Detailstemp)


UPDATE    tbl_Gl_account
SET GL_Balance = GL_Balance + (SELECT decGLTransAmount2 FROM @LIST)
WHERE (GL_Account_id = (SELECT intGLAccountId FROM @LIST))

DECLARE @Journal_Master_Id int
DECLARE @Trans_Id int
DECLARE @Amount decimal
DECLARE @Memo varchar(50)

SET @Journal_Master_Id=(SELECT Journal_Master_Id FROM @LIST)
SET @Trans_Id=(@id)
SET @Amount=(SELECT decGLTransAmount FROM @LIST)
SET @Memo=(SELECT strJournalmemo FROM @LIST)

INSERT INTO tbl_Journal_Details
    (Journal_Master_Id, Trans_Id, Amount, Memo)
VALUES     (@Journal_Master_Id,@Trans_Id,@Amount,@Memo)

END
RETURN

Iterating between the rows of a Table valued parameter

Answers (1)

Related Questions