anil john

anil john

  • NA
  • 156
  • 208.6k

update query

Sep 6 2012 7:16 AM
i have written a query in sp for update, but am getting result as the new value is appended to the old value insted of the new vale, why it happens?
my sp:

ALTER PROCEDURE dbo.sp_UserRights
    (
    @iUserLevel int,
    @vAddUser varchar(50),
    @vBalance varchar(50),
    @vBankofAccounts varchar(50),
    @vBranchMaster varchar(50),
    @vChittyAuction varchar(50),
    @vChittyGeneralReciept varchar(50),
    @vChittyInstallments varchar(50),
    @vChittyMaster varchar(50),
    @vChittyPayments varchar(50),
    @vChittyReciepts varchar(50),
    @vChittyRegistration varchar(50),
    @vChittyReport varchar(50),
    @vCompanyMaster varchar(50),
    @vContra varchar(50),
    @vCustomerLedger varchar(50),
    @vCustomerMaster varchar(50),
    @vDueList varchar(50),
    @vEmployeeMaster varchar(50),
    @vGeneralPayments varchar(50),
    @vGeneralReciept varchar(50),
    @vInstallmentCreation varchar(50),
    @vJournelEntry varchar(50),
    @vLogin varchar(50),
    @vMAGNA varchar(50),
    @vMAGNA_BRANCH varchar(50),
    @vPaymentDetails varchar(50),
    @vPrintBankAcc varchar(50),
    @vPrintPandL varchar(50),
    @vPrintTrailBalance varchar(50),
    @vReportAccountLedger varchar(50),
    @vUserPrevilege varchar(50),
    @vVoucherConfiguration varchar(50),
    @vVoucherListView varchar(50),
    @blockType int,
    @TempID int
    )
AS
SET NOCOUNT ON
    IF(@blockType=0)
    Begin
    IF EXISTS(SELECT * FROM tblUserRights WHERE iUserLevel=@iUserLevel AND TempID=@TempID)
    BEGIN
    RETURN
    END
    ELSE
    BEGIN
    INSERT INTO tblUserRights(iUserLevel,vAddUser,vBalance,vBankofAccounts,vBranchMaster,vChittyAuction,vChittyGeneralReciept,
                                vChittyInstallments,vChittyMaster,vChittyPayments,vChittyReciepts,vChittyRegistration,
                                vChittyReport,vCompanyMaster,vContra,vCustomerLedger,vCustomerMaster,vDueList,vEmployeeMaster,
                                vGeneralPayments,vGeneralReciept,vInstallmentCreation,vJournelEntry,vLogin,vMAGNA,vMAGNA_BRANCH,
                                vPaymentDetails,vPrintBankAcc,vPrintPandL,vPrintTrailBalance,vReportAccountLedger,vUserPrevilege,
                                vVoucherConfiguration,vVoucherListView)
                        VALUES(@iUserLevel,@vAddUser,@vBalance,@vBankofAccounts,@vBranchMaster,@vChittyAuction,@vChittyGeneralReciept,
                                @vChittyInstallments,@vChittyMaster,@vChittyPayments,@vChittyReciepts,
                                @vChittyRegistration,@vChittyReport,@vCompanyMaster,@vContra,@vCustomerLedger,@vCustomerMaster,@vDueList,
                                @vEmployeeMaster,@vGeneralPayments,@vGeneralReciept,@vInstallmentCreation,@vJournelEntry,@vLogin,@vMAGNA,
                                @vMAGNA_BRANCH,@vPaymentDetails,@vPrintBankAcc,@vPrintPandL,@vPrintTrailBalance,@vReportAccountLedger,
                                @vUserPrevilege,@vVoucherConfiguration,@vVoucherListView)
    RETURN
    END
    End
    ELSE IF(@blockType=1)
    Begin
    UPDATE tblUserRights SET vAddUser=@vAddUser,vBalance=@vBalance,vBankofAccounts=@vBankofAccounts,vBranchMaster=@vBranchMaster,
                            vChittyAuction=@vChittyAuction,vChittyGeneralReciept=@vChittyGeneralReciept,vChittyInstallments=@vChittyInstallments,
                            vChittyMaster=@vChittyMaster,vChittyPayments=@vChittyPayments,vChittyReciepts=@vChittyReciepts,
                            vChittyRegistration=@vChittyRegistration,vChittyReport=@vChittyReport,vCompanyMaster=@vCompanyMaster,
                            vContra=@vContra,vCustomerLedger=@vCustomerLedger,vCustomerMaster=@vCustomerMaster,vDueList=@vDueList,
                            vEmployeeMaster=@vEmployeeMaster,vGeneralPayments=@vGeneralPayments,vGeneralReciept=@vGeneralReciept,
                            vInstallmentCreation=@vInstallmentCreation,vJournelEntry=@vJournelEntry,vLogin=@vLogin,vMAGNA=@vMAGNA,
                            vMAGNA_BRANCH=@vMAGNA_BRANCH,vPaymentDetails=@vPaymentDetails,vPrintBankAcc=@vPrintBankAcc,vPrintPandL=@vPrintPandL,   
                            vPrintTrailBalance=@vPrintTrailBalance,vReportAccountLedger=@vReportAccountLedger,vUserPrevilege=@vUserPrevilege,
                            vVoucherConfiguration=@vVoucherConfiguration,vVoucherListView=@vVoucherListView WHERE iUserLevel=@iUserLevel AND TempID=@TempID               
    SELECT @TempID As RowID                           
    End
    RETURN


Answers (2)