storedprocedure
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?
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Sukesh MarlaPosted Sep 6, 2012, 12:29 PM
Akkiraju IvaturiPosted Sep 6, 2012, 7:31 AM
anil johnPosted Sep 6, 2012, 7:03 AM
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
Sukesh MarlaPosted Sep 6, 2012, 6:30 AM