July 20, 2016 at 12:52 am
Hi,
In continuation with a previous topic I asked I still have issue with a new dynamic query (this is kind a new subject for me), :w00t:
I have a few empty tables that they look all the same
--drop table Finiti
create table Finiti
(
ID int identity primary key
,DateCreated datetime NOT NULL DEFAULT(GETDATE())
,UserName varchar (50) NOT NULL
,CompanyName varchar (50) NOT NULL
,IP varchar (50)
,RequestNumber varchar(40) DEFAULT(NEWID())
,ModelType int
,AP_Customer_IDint
,AP_Application_IDint
,BH_PastCompletedLoans_CNTint
,BH_CurrentLoans_CNTint
,BH_PastdueMaxNumRefuse_CNTint
,BH_LegalIndex_FLGint
,BH_LoanRefuse_FLGint
,LO_LoanRequest_AMTfloat
,LO_LoanObjective_CDint
,LO_LoanPeriod_CNTint
,LO_LoanAmortSchedule_CDint
,Cust_Age_CNTint
,Cust_Genderint
,Cust_MaritalStatus_CDint
,Cust_Num_Child_22_Below_CNT int
,Cust_Education_CD int
,Cust_EmploymentType_CDint
,Cust_WorkType_CDint
,Cust_City_CDint
,Cust_Street_CDint
,Cust_addressNum_CNTint
,Cust_ZipCode_CNTint
,Cust_EmploymentSeniority_CNT int
,Fin_CustNetIncome_AMTfloat
,Fin_HouseholdNetIncome_AMTfloat
,Fin_ResidenceOwnership_FLGint
,Fin_Residential_Lease_AMTfloat
,Fin_Residential_Mortgage_AMTfloat
,Fin_OtherLoansPayMonthly_AMT float
,Fin_CreditCardOwnership_FLGint
,FIN_FinanceInvest_CDint
,COL_CollateralType_CDint
,COL_SavingAccountMonthly_AMTfloat
,COL_SavingAccount_AMTfloat
,COL_SavingAccountLiq_AMTfloat
,COL_SavingAccountLiq_DTdatetime
,COL_IshtalmutMonthly_AMTfloat
,COL_Ishtalmut_AMTfloat
,COL_IshtalmutLiq_AMTfloat
,COL_IshtalmutLiq_DTdatetime
,Cust_BankDeposit_CDint
,Cust_BranchDeposit_CDint
,Cust_BankWithdraw_CDint
,Cust_BranchWithdraw_CDint
,Cust_Active_Status_FLGint
,Dirug varchar(5)
)
I need to populate the table, but I will know exactly which table to populate once I receive the company name.
I tried two version of the procedure and it is not working
version one
alter procedure insert_data_extendedmodel_sp
@UserName varchar (50)
,@CompanyName varchar (50)
,@IP varchar (50)
,@AP_Customer_IDint
,@AP_Application_IDint
,@BH_PastCompletedLoans_CNTint
,@BH_CurrentLoans_CNTint
,@BH_PastdueMaxNumRefuse_CNTint
,@BH_LegalIndex_FLGint
,@BH_LoanRefuse_FLGint
,@LO_LoanRequest_AMTfloat
,@LO_LoanObjective_CDint
,@LO_LoanPeriod_CNTint
,@LO_LoanAmortSchedule_CDint
,@Cust_Age_CNTint
,@Cust_Genderint
,@Cust_MaritalStatus_CDint
,@Cust_Num_Child_22_Below_CNT int
,@Cust_Education_CD int
,@Cust_EmploymentType_CDint
,@Cust_WorkType_CDint
,@Cust_City_CDint
,@Cust_Street_CDint
,@Cust_addressNum_CNTint
,@Cust_ZipCode_CNTint
,@Cust_EmploymentSeniority_CNT int
,@Fin_CustNetIncome_AMTfloat
,@Fin_HouseholdNetIncome_AMTfloat
,@Fin_ResidenceOwnership_FLGint
,@Fin_Residential_Lease_AMTfloat
,@Fin_Residential_Mortgage_AMTfloat
,@Fin_OtherLoansPayMonthly_AMT float
,@Fin_CreditCardOwnership_FLGint
,@FIN_FinanceInvest_CDint
,@COL_CollateralType_CDint
,@COL_SavingAccountMonthly_AMTfloat
,@COL_SavingAccount_AMTfloat
,@COL_SavingAccountLiq_AMTfloat
,@COL_SavingAccountLiq_DTdatetime
,@COL_IshtalmutMonthly_AMTfloat
,@COL_Ishtalmut_AMTfloat
,@COL_IshtalmutLiq_AMTfloat
,@COL_IshtalmutLiq_DTdatetime
,@Cust_BankDeposit_CDint
,@Cust_BranchDeposit_CDint
,@Cust_BankWithdraw_CDint
,@Cust_BranchWithdraw_CDint
,@Cust_Active_Status_FLGint
,@Dirug varchar(5)
as
begin
declare @TableName nvarchar(200), @sqlquery nvarchar(max)
select @TableName = name from sys.objects where type = 'u' and name = @CompanyName
set @sqlquery = 'insert into ' + @TableName + ' (UserName, CompanyName, IP, ModelType,AP_Customer_ID ,AP_Application_ID, BH_PastCompletedLoans_CNT ,BH_CurrentLoans_CNT ,BH_PastdueMaxNumRefuse_CNT
,BH_LegalIndex_FLG ,BH_LoanRefuse_FLG ,LO_LoanRequest_AMT ,LO_LoanObjective_CD ,LO_LoanPeriod_CNT ,LO_LoanAmortSchedule_CD ,Cust_Age_CNT
,Cust_Gender ,Cust_MaritalStatus_CD,Cust_Num_Child_22_Below_CNT ,Cust_Education_CD ,Cust_EmploymentType_CD, Cust_WorkType_CD ,Cust_City_CD,Cust_Street_CD
,Cust_addressNum_CNT, Cust_ZipCode_CNT ,Cust_EmploymentSeniority_CNT ,Fin_CustNetIncome_AMT,Fin_HouseholdNetIncome_AMT,Fin_ResidenceOwnership_FLG,Fin_Residential_Lease_AMT
,Fin_Residential_Mortgage_AMT ,Fin_OtherLoansPayMonthly_AMT ,Fin_CreditCardOwnership_FLG ,FIN_FinanceInvest_CD ,COL_CollateralType_CD ,COL_SavingAccountMonthly_AMT
,COL_SavingAccount_AMT ,COL_SavingAccountLiq_AMT ,COL_SavingAccountLiq_DT ,COL_IshtalmutMonthly_AMT ,COL_Ishtalmut_AMT ,COL_IshtalmutLiq_AMT ,COL_IshtalmutLiq_DT
,Cust_BankDeposit_CD ,Cust_BranchDeposit_CD,Cust_BankWithdraw_CD ,Cust_BranchWithdraw_CD ,Cust_Active_Status_FLG ,Dirug) values ('@UserName + ',' + @CompanyName
,@IP + ',1 ,' + @AP_Customer_ID + ',' + @AP_Application_ID + ',' + @BH_PastCompletedLoans_CNT + ',' + @BH_CurrentLoans_CNT+ ',' + @BH_PastdueMaxNumRefuse_CNT
+ ',' +@BH_LegalIndex_FLG + ',' +@BH_LoanRefuse_FLG+ ',' + @LO_LoanRequest_AMT + ',' + @LO_LoanObjective_CD + ',' + @LO_LoanPeriod_CNT+ ',' +@LO_LoanAmortSchedule_CD + ',' + @Cust_Age_CNT
+ ',' +@Cust_Gender + ',' +@Cust_MaritalStatus_CD + ',' +@Cust_Num_Child_22_Below_CNT+ ',' +@Cust_Education_CD + ',' +,@Cust_EmploymentType_CD+ ',' +@Cust_WorkType_CD+ ',' +@Cust_City_CD
+ ',' +@Cust_Street_CD ,@Cust_addressNum_CNT+ ',' +@Cust_ZipCode_CNT+ ',' +@Cust_EmploymentSeniority_CNT + ',' +@Fin_CustNetIncome_AMT+ ',' +@Fin_HouseholdNetIncome_AMT
+ ',' +@Fin_ResidenceOwnership_FLG + ',' +@Fin_Residential_Lease_AMT + ',' +@Fin_Residential_Mortgage_AMT + ',' +@Fin_OtherLoansPayMonthly_AMT + ',' +@Fin_CreditCardOwnership_FLG
+ ',' +@FIN_FinanceInvest_CD + ',' +@COL_CollateralType_CD + ',' +@COL_SavingAccountMonthly_AMT + ',' +@COL_SavingAccount_AMT + ',' + @COL_SavingAccountLiq_AMT + ',' + CAST(@COL_SavingAccountLiq_DT as nvarchar(11))
+ ',' +@COL_IshtalmutMonthly_AMT + ',' +@COL_Ishtalmut_AMT + ',' +@COL_IshtalmutLiq_AMT + ',' + CAST(@COL_IshtalmutLiq_DT as nvarchar(11))+ ',' + @Cust_BankDeposit_CD + ',' + @Cust_BranchDeposit_CD
+ ',' +@Cust_BankWithdraw_CD + ',' +@Cust_BranchWithdraw_CD + ',' +@Cust_Active_Status_FLG+ ',' + @Dirug)''
PRINT @sqlquery
exec sp_executesql @sqlquery
end
version two
alter procedure insert_data_extendedmodel_sp
@UserName varchar (50)
,@CompanyName varchar (50)
,@IP varchar (50)
,@AP_Customer_IDint
,@AP_Application_IDint
,@BH_PastCompletedLoans_CNTint
,@BH_CurrentLoans_CNTint
,@BH_PastdueMaxNumRefuse_CNTint
,@BH_LegalIndex_FLGint
,@BH_LoanRefuse_FLGint
,@LO_LoanRequest_AMTfloat
,@LO_LoanObjective_CDint
,@LO_LoanPeriod_CNTint
,@LO_LoanAmortSchedule_CDint
,@Cust_Age_CNTint
,@Cust_Genderint
,@Cust_MaritalStatus_CDint
,@Cust_Num_Child_22_Below_CNT int
,@Cust_Education_CD int
,@Cust_EmploymentType_CDint
,@Cust_WorkType_CDint
,@Cust_City_CDint
,@Cust_Street_CDint
,@Cust_addressNum_CNTint
,@Cust_ZipCode_CNTint
,@Cust_EmploymentSeniority_CNT int
,@Fin_CustNetIncome_AMTfloat
,@Fin_HouseholdNetIncome_AMTfloat
,@Fin_ResidenceOwnership_FLGint
,@Fin_Residential_Lease_AMTfloat
,@Fin_Residential_Mortgage_AMTfloat
,@Fin_OtherLoansPayMonthly_AMT float
,@Fin_CreditCardOwnership_FLGint
,@FIN_FinanceInvest_CDint
,@COL_CollateralType_CDint
,@COL_SavingAccountMonthly_AMTfloat
,@COL_SavingAccount_AMTfloat
,@COL_SavingAccountLiq_AMTfloat
,@COL_SavingAccountLiq_DTdatetime
,@COL_IshtalmutMonthly_AMTfloat
,@COL_Ishtalmut_AMTfloat
,@COL_IshtalmutLiq_AMTfloat
,@COL_IshtalmutLiq_DTdatetime
,@Cust_BankDeposit_CDint
,@Cust_BranchDeposit_CDint
,@Cust_BankWithdraw_CDint
,@Cust_BranchWithdraw_CDint
,@Cust_Active_Status_FLGint
,@Dirug varchar(5)
as
begin
declare @TableName nvarchar(200), @sqlquery nvarchar(max)
select @TableName = name from sys.objects where type = 'u' and name = @CompanyName
set @sqlquery = 'insert into ' + @TableName + ' (UserName, CompanyName, IP, ModelType,AP_Customer_ID ,AP_Application_ID, BH_PastCompletedLoans_CNT ,BH_CurrentLoans_CNT ,BH_PastdueMaxNumRefuse_CNT
,BH_LegalIndex_FLG ,BH_LoanRefuse_FLG ,LO_LoanRequest_AMT ,LO_LoanObjective_CD ,LO_LoanPeriod_CNT ,LO_LoanAmortSchedule_CD ,Cust_Age_CNT
,Cust_Gender ,Cust_MaritalStatus_CD,Cust_Num_Child_22_Below_CNT ,Cust_Education_CD ,Cust_EmploymentType_CD, Cust_WorkType_CD ,Cust_City_CD,Cust_Street_CD
,Cust_addressNum_CNT, Cust_ZipCode_CNT ,Cust_EmploymentSeniority_CNT ,Fin_CustNetIncome_AMT,Fin_HouseholdNetIncome_AMT,Fin_ResidenceOwnership_FLG,Fin_Residential_Lease_AMT
,Fin_Residential_Mortgage_AMT ,Fin_OtherLoansPayMonthly_AMT ,Fin_CreditCardOwnership_FLG ,FIN_FinanceInvest_CD ,COL_CollateralType_CD ,COL_SavingAccountMonthly_AMT
,COL_SavingAccount_AMT ,COL_SavingAccountLiq_AMT ,COL_SavingAccountLiq_DT ,COL_IshtalmutMonthly_AMT ,COL_Ishtalmut_AMT ,COL_IshtalmutLiq_AMT ,COL_IshtalmutLiq_DT
,Cust_BankDeposit_CD ,Cust_BranchDeposit_CD,Cust_BankWithdraw_CD ,Cust_BranchWithdraw_CD ,Cust_Active_Status_FLG ,Dirug) values (@UserName ,@CompanyName
,@IP, ''1'', @AP_Customer_ID ,@AP_Application_ID ,@BH_PastCompletedLoans_CNT ,@BH_CurrentLoans_CNT, @BH_PastdueMaxNumRefuse_CNT
,@BH_LegalIndex_FLG,@BH_LoanRefuse_FLG,@LO_LoanRequest_AMT ,@LO_LoanObjective_CD, @LO_LoanPeriod_CNT, @LO_LoanAmortSchedule_CD, @Cust_Age_CNT
,@Cust_Gender ,@Cust_MaritalStatus_CD ,@Cust_Num_Child_22_Below_CNT ,@Cust_Education_CD ,@Cust_EmploymentType_CD ,@Cust_WorkType_CD,@Cust_City_CD
,@Cust_Street_CD ,@Cust_addressNum_CNT ,@Cust_ZipCode_CNT ,@Cust_EmploymentSeniority_CNT ,@Fin_CustNetIncome_AMT ,@Fin_HouseholdNetIncome_AMT
,@Fin_ResidenceOwnership_FLG ,@Fin_Residential_Lease_AMT ,@Fin_Residential_Mortgage_AMT,@Fin_OtherLoansPayMonthly_AMT ,@Fin_CreditCardOwnership_FLG
,@FIN_FinanceInvest_CD ,@COL_CollateralType_CD ,@COL_SavingAccountMonthly_AMT ,@COL_SavingAccount_AMT ,@COL_SavingAccountLiq_AMT ,''' + CAST(@COL_SavingAccountLiq_DT as nvarchar(11))
+ ''',@COL_IshtalmutMonthly_AMT ,@COL_Ishtalmut_AMT ,@COL_IshtalmutLiq_AMT ,''' + CAST(@COL_IshtalmutLiq_DT as nvarchar(11)) + ''',@Cust_BankDeposit_CD ,@Cust_BranchDeposit_CD
,@Cust_BankWithdraw_CD ,@Cust_BranchWithdraw_CD,@Cust_Active_Status_FLG ,@Dirug)'
PRINT @sqlquery
exec sp_executesql @stmt = @sqlquery, @UserName = @UserName, @CompanyName = @CompanyName, @IP = @IP, @AP_Customer_ID = @AP_Customer_ID ,@AP_Application_ID = @AP_Application_ID,
@BH_PastCompletedLoans_CNT = @BH_PastCompletedLoans_CNT ,@BH_CurrentLoans_CNT = @BH_CurrentLoans_CNT, @BH_PastdueMaxNumRefuse_CNT = @BH_PastdueMaxNumRefuse_CNT,
@BH_LegalIndex_FLG= @BH_LegalIndex_FLG, @BH_LoanRefuse_FLG = @BH_LoanRefuse_FLG, @LO_LoanRequest_AMT = @LO_LoanRequest_AMT, @LO_LoanObjective_CD = @LO_LoanObjective_CD,
@LO_LoanPeriod_CNT = @LO_LoanPeriod_CNT, @LO_LoanAmortSchedule_CD = @LO_LoanAmortSchedule_CD, @Cust_Age_CNT = @Cust_Age_CNT, @Cust_Gender = @Cust_Gender,
@Cust_MaritalStatus_CD = @Cust_MaritalStatus_CD,@Cust_Num_Child_22_Below_CNT = @Cust_Num_Child_22_Below_CNT, @Cust_Education_CD = @Cust_Education_CD,
@Cust_EmploymentType_CD = @Cust_EmploymentType_CD, @Cust_WorkType_CD = @Cust_WorkType_CD, @Cust_City_CD= @Cust_City_CD ,@Cust_Street_CD = @Cust_Street_CD,
@Cust_addressNum_CNT = @Cust_addressNum_CNT, @Cust_ZipCode_CNT = @Cust_ZipCode_CNT, @Cust_EmploymentSeniority_CNT = @Cust_EmploymentSeniority_CNT,
@Fin_CustNetIncome_AMT = @Fin_CustNetIncome_AMT, @Fin_HouseholdNetIncome_AMT = @Fin_HouseholdNetIncome_AMT, @Fin_ResidenceOwnership_FLG = @Fin_ResidenceOwnership_FLG,
@Fin_Residential_Lease_AMT = @Fin_Residential_Lease_AMT, @Fin_Residential_Mortgage_AMT = @Fin_Residential_Mortgage_AMT, @Fin_OtherLoansPayMonthly_AMT = @Fin_OtherLoansPayMonthly_AMT,
@Fin_CreditCardOwnership_FLG = @Fin_CreditCardOwnership_FLG, @FIN_FinanceInvest_CD = @FIN_FinanceInvest_CD, @COL_CollateralType_CD = @COL_CollateralType_CD,
@COL_SavingAccountMonthly_AMT = @COL_SavingAccountMonthly_AMT, @COL_SavingAccount_AMT = @COL_SavingAccount_AMT, @COL_SavingAccountLiq_AMT = @COL_SavingAccountLiq_AMT,
@COL_SavingAccountLiq_DT = @COL_SavingAccountLiq_DT, @COL_IshtalmutMonthly_AMT = @COL_IshtalmutMonthly_AMT, @COL_Ishtalmut_AMT = @COL_Ishtalmut_AMT,
@COL_IshtalmutLiq_AMT = @COL_IshtalmutLiq_AMT, @COL_IshtalmutLiq_DT = @COL_IshtalmutLiq_DT, @Cust_BankDeposit_CD = @Cust_BankDeposit_CD,
@Cust_BranchDeposit_CD = @Cust_BranchDeposit_CD, @Cust_BankWithdraw_CD = @Cust_BankWithdraw_CD, @Cust_BranchWithdraw_CD = @Cust_BranchWithdraw_CD,
@Cust_Active_Status_FLG = @Cust_Active_Status_FLG, @Cust_Active_Status_FLG = @Cust_Active_Status_FLG, @Dirug = @Dirug
end
where where where i am going wrong.... :unsure:
July 20, 2016 at 1:26 am
what kind of error you are getting? in both of your versions ?
Btw, In your 1st version (Assuming there is no typo why copy t-sql) you have syntax errors.
July 20, 2016 at 1:45 am
the first version is a guess, more or less, when i run the second version i get
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'Finiti'.
i do have syntax error, otherwise it would be working :crying: :crying: :crying:
July 20, 2016 at 1:52 am
It looks as though you're also going to have problems when handling strings / varchar() fields - you've not wrapped the values in quotes, so you'll generate an insert statement like:
INSERT INTO tablename (CompanyName) VALUES (Fortnum & Snellgrove);
which isn't going to work.
Look at using the QUOTENAME function when building your string:
D ECLARE @CompanyName varchar(40) = 'Fortnum & Snellgrove')
D ECLARE @SQL varchar(max) = ''
S ELECT @SQL = 'INSERT INTO tablename (CompanyName) VALUES (' + QUOTENAME(@CompanyName, '''') + ');'
That will generate code that should work. (Once you've corrected the first word of each line here - poxy proxy server overheats if I send in too much T-SQL in an answer...)
Be aware of possible SQL Injection attacks too. And if you want to get really paranoid (which you should), then check out Alex Kuznetsov's book "Defensive Database Programming", available at the Redgate Bookstore as a free download (well, free-ish - I think you might have to provide your email address...)
Thomas Rushton
blog: https://thelonedba.wordpress.com
July 20, 2016 at 2:11 am
astrid 69000 (7/20/2016)
the first version is a guess, more or less, when i run the second version i getMsg 102, Level 15, State 1, Line 3
Incorrect syntax near 'Finiti'.
i do have syntax error, otherwise it would be working :crying: :crying: :crying:
i recommend that you use the second version, as in 1st version you will have to CAST each variable into NVARCHAR to complete the query.
Now your 2nd version, you need to send the list of Param to sp_executesql in order to execute it. Like below.
Note:
If you are using a Parameters Dynamic Query you should never type cast it in your dynamic query.
alter procedure insert_data_extendedmodel_sp
@UserName varchar (50)
,@CompanyName varchar (50)
,@IP varchar (50)
,@AP_Customer_IDint
,@AP_Application_IDint
,@BH_PastCompletedLoans_CNTint
,@BH_CurrentLoans_CNTint
,@BH_PastdueMaxNumRefuse_CNTint
,@BH_LegalIndex_FLGint
,@BH_LoanRefuse_FLGint
,@LO_LoanRequest_AMTfloat
,@LO_LoanObjective_CDint
,@LO_LoanPeriod_CNTint
,@LO_LoanAmortSchedule_CDint
,@Cust_Age_CNTint
,@Cust_Genderint
,@Cust_MaritalStatus_CDint
,@Cust_Num_Child_22_Below_CNT int
,@Cust_Education_CD int
,@Cust_EmploymentType_CDint
,@Cust_WorkType_CDint
,@Cust_City_CDint
,@Cust_Street_CDint
,@Cust_addressNum_CNTint
,@Cust_ZipCode_CNTint
,@Cust_EmploymentSeniority_CNT int
,@Fin_CustNetIncome_AMTfloat
,@Fin_HouseholdNetIncome_AMTfloat
,@Fin_ResidenceOwnership_FLGint
,@Fin_Residential_Lease_AMTfloat
,@Fin_Residential_Mortgage_AMTfloat
,@Fin_OtherLoansPayMonthly_AMT float
,@Fin_CreditCardOwnership_FLGint
,@FIN_FinanceInvest_CDint
,@COL_CollateralType_CDint
,@COL_SavingAccountMonthly_AMTfloat
,@COL_SavingAccount_AMTfloat
,@COL_SavingAccountLiq_AMTfloat
,@COL_SavingAccountLiq_DTdatetime
,@COL_IshtalmutMonthly_AMTfloat
,@COL_Ishtalmut_AMTfloat
,@COL_IshtalmutLiq_AMTfloat
,@COL_IshtalmutLiq_DTdatetime
,@Cust_BankDeposit_CDint
,@Cust_BranchDeposit_CDint
,@Cust_BankWithdraw_CDint
,@Cust_BranchWithdraw_CDint
,@Cust_Active_Status_FLGint
,@Dirug varchar(5)
as
begin
declare @TableName nvarchar(200), @sqlquery nvarchar(max)
select @TableName = name from sys.objects where type = 'u' and name = @CompanyName
set @sqlquery = 'insert into ' + cast(@TableName as nvarchar(max)) + ' (
[UserName], [CompanyName], 127.0.0.1, [ModelType], [AP_Customer_ID], [AP_Application_ID], [BH_PastCompletedLoans_CNT], [BH_CurrentLoans_CNT], [BH_PastdueMaxNumRefuse_CNT]
, [BH_LegalIndex_FLG], [BH_LoanRefuse_FLG], [LO_LoanRequest_AMT], [LO_LoanObjective_CD], [LO_LoanPeriod_CNT], [LO_LoanAmortSchedule_CD], [Cust_Age_CNT]
, [Cust_Gender], [Cust_MaritalStatus_CD], [Cust_Num_Child_22_Below_CNT], [Cust_Education_CD], [Cust_EmploymentType_CD], [Cust_WorkType_CD], [Cust_City_CD]
, [Cust_Street_CD], [Cust_addressNum_CNT], [Cust_ZipCode_CNT], [Cust_EmploymentSeniority_CNT], [Fin_CustNetIncome_AMT], [Fin_HouseholdNetIncome_AMT]
, [Fin_ResidenceOwnership_FLG], [Fin_Residential_Lease_AMT], [Fin_Residential_Mortgage_AMT], [Fin_OtherLoansPayMonthly_AMT], [Fin_CreditCardOwnership_FLG]
, [FIN_FinanceInvest_CD], [COL_CollateralType_CD], [COL_SavingAccountMonthly_AMT], [COL_SavingAccount_AMT], [COL_SavingAccountLiq_AMT], [COL_SavingAccountLiq_DT]
, [COL_IshtalmutMonthly_AMT], [COL_Ishtalmut_AMT], [COL_IshtalmutLiq_AMT], [COL_IshtalmutLiq_DT], [Cust_BankDeposit_CD], [Cust_BranchDeposit_CD]
, [Cust_BankWithdraw_CD], [Cust_BranchWithdraw_CD], [Cust_Active_Status_FLG], [Dirug]
)
values (
@UserName, @CompanyName, @IP, ''1'', @AP_Customer_ID, @AP_Application_ID, @BH_PastCompletedLoans_CNT, @BH_CurrentLoans_CNT, @BH_PastdueMaxNumRefuse_CNT
, @BH_LegalIndex_FLG, @BH_LoanRefuse_FLG, @LO_LoanRequest_AMT, @LO_LoanObjective_CD, @LO_LoanPeriod_CNT, @LO_LoanAmortSchedule_CD, @Cust_Age_CNT
, @Cust_Gender, @Cust_MaritalStatus_CD, @Cust_Num_Child_22_Below_CNT, @Cust_Education_CD, @Cust_EmploymentType_CD, @Cust_WorkType_CD, @Cust_City_CD
, @Cust_Street_CD, @Cust_addressNum_CNT, @Cust_ZipCode_CNT, @Cust_EmploymentSeniority_CNT, @Fin_CustNetIncome_AMT, @Fin_HouseholdNetIncome_AMT
, @Fin_ResidenceOwnership_FLG, @Fin_Residential_Lease_AMT, @Fin_Residential_Mortgage_AMT, @Fin_OtherLoansPayMonthly_AMT, @Fin_CreditCardOwnership_FLG
, @FIN_FinanceInvest_CD, @COL_CollateralType_CD, @COL_SavingAccountMonthly_AMT, @COL_SavingAccount_AMT, @COL_SavingAccountLiq_AMT, @COL_SavingAccountLiq_DT
, @COL_IshtalmutMonthly_AMT, @COL_Ishtalmut_AMT, @COL_IshtalmutLiq_AMT, @COL_IshtalmutLiq_DT, @Cust_BankDeposit_CD
, @Cust_BranchDeposit_CD, @Cust_BankWithdraw_CD, @Cust_BranchWithdraw_CD, @Cust_Active_Status_FLG, @Dirug
)'
PRINT @sqlquery
exec sp_executesql @stmt = @sqlquery, @param =
N'
@UserName varchar (50)
,@CompanyName varchar (50)
,@IP varchar (50)
,@AP_Customer_IDint
,@AP_Application_IDint
,@BH_PastCompletedLoans_CNTint
,@BH_CurrentLoans_CNTint
,@BH_PastdueMaxNumRefuse_CNTint
,@BH_LegalIndex_FLGint
,@BH_LoanRefuse_FLGint
,@LO_LoanRequest_AMTfloat
,@LO_LoanObjective_CDint
,@LO_LoanPeriod_CNTint
,@LO_LoanAmortSchedule_CDint
,@Cust_Age_CNTint
,@Cust_Genderint
,@Cust_MaritalStatus_CDint
,@Cust_Num_Child_22_Below_CNT int
,@Cust_Education_CD int
,@Cust_EmploymentType_CDint
,@Cust_WorkType_CDint
,@Cust_City_CDint
,@Cust_Street_CDint
,@Cust_addressNum_CNTint
,@Cust_ZipCode_CNTint
,@Cust_EmploymentSeniority_CNT int
,@Fin_CustNetIncome_AMTfloat
,@Fin_HouseholdNetIncome_AMTfloat
,@Fin_ResidenceOwnership_FLGint
,@Fin_Residential_Lease_AMTfloat
,@Fin_Residential_Mortgage_AMTfloat
,@Fin_OtherLoansPayMonthly_AMT float
,@Fin_CreditCardOwnership_FLGint
,@FIN_FinanceInvest_CDint
,@COL_CollateralType_CDint
,@COL_SavingAccountMonthly_AMTfloat
,@COL_SavingAccount_AMTfloat
,@COL_SavingAccountLiq_AMTfloat
,@COL_SavingAccountLiq_DTdatetime
,@COL_IshtalmutMonthly_AMTfloat
,@COL_Ishtalmut_AMTfloat
,@COL_IshtalmutLiq_AMTfloat
,@COL_IshtalmutLiq_DTdatetime
,@Cust_BankDeposit_CDint
,@Cust_BranchDeposit_CDint
,@Cust_BankWithdraw_CDint
,@Cust_BranchWithdraw_CDint
,@Cust_Active_Status_FLGint
,@Dirug varchar(5)
'
,
@UserName= @UserName
,@CompanyName= @CompanyName
,@IP= @IP
,@AP_Customer_ID= @AP_Customer_ID
,@AP_Application_ID= @AP_Application_ID
,@BH_PastCompletedLoans_CNT= @BH_PastCompletedLoans_CNT
,@BH_CurrentLoans_CNT= @BH_CurrentLoans_CNT
,@BH_PastdueMaxNumRefuse_CNT= @BH_PastdueMaxNumRefuse_CNT
,@BH_LegalIndex_FLG= @BH_LegalIndex_FLG
,@BH_LoanRefuse_FLG= @BH_LoanRefuse_FLG
,@LO_LoanRequest_AMT= @LO_LoanRequest_AMT
,@LO_LoanObjective_CD= @LO_LoanObjective_CD
,@LO_LoanPeriod_CNT= @LO_LoanPeriod_CNT
,@LO_LoanAmortSchedule_CD= @LO_LoanAmortSchedule_CD
,@Cust_Age_CNT= @Cust_Age_CNT
,@Cust_Gender= @Cust_Gender
,@Cust_MaritalStatus_CD= @Cust_MaritalStatus_CD
,@Cust_Num_Child_22_Below_CNT= @Cust_Num_Child_22_Below_CNT
,@Cust_Education_CD= @Cust_Education_CD
,@Cust_EmploymentType_CD= @Cust_EmploymentType_CD
,@Cust_WorkType_CD= @Cust_WorkType_CD
,@Cust_City_CD= @Cust_City_CD
,@Cust_Street_CD= @Cust_Street_CD
,@Cust_addressNum_CNT= @Cust_addressNum_CNT
,@Cust_ZipCode_CNT= @Cust_ZipCode_CNT
,@Cust_EmploymentSeniority_CNT= @Cust_EmploymentSeniority_CNT
,@Fin_CustNetIncome_AMT= @Fin_CustNetIncome_AMT
,@Fin_HouseholdNetIncome_AMT= @Fin_HouseholdNetIncome_AMT
,@Fin_ResidenceOwnership_FLG= @Fin_ResidenceOwnership_FLG
,@Fin_Residential_Lease_AMT= @Fin_Residential_Lease_AMT
,@Fin_Residential_Mortgage_AMT= @Fin_Residential_Mortgage_AMT
,@Fin_OtherLoansPayMonthly_AMT= @Fin_OtherLoansPayMonthly_AMT
,@Fin_CreditCardOwnership_FLG= @Fin_CreditCardOwnership_FLG
,@FIN_FinanceInvest_CD= @FIN_FinanceInvest_CD
,@COL_CollateralType_CD= @COL_CollateralType_CD
,@COL_SavingAccountMonthly_AMT= @COL_SavingAccountMonthly_AMT
,@COL_SavingAccount_AMT= @COL_SavingAccount_AMT
,@COL_SavingAccountLiq_AMT= @COL_SavingAccountLiq_AMT
,@COL_SavingAccountLiq_DT= @COL_SavingAccountLiq_DT
,@COL_IshtalmutMonthly_AMT= @COL_IshtalmutMonthly_AMT
,@COL_Ishtalmut_AMT= @COL_Ishtalmut_AMT
,@COL_IshtalmutLiq_AMT= @COL_IshtalmutLiq_AMT
,@COL_IshtalmutLiq_DT= @COL_IshtalmutLiq_DT
,@Cust_BankDeposit_CD= @Cust_BankDeposit_CD
,@Cust_BranchDeposit_CD= @Cust_BranchDeposit_CD
,@Cust_BankWithdraw_CD= @Cust_BankWithdraw_CD
,@Cust_BranchWithdraw_CD= @Cust_BranchWithdraw_CD
,@Cust_Active_Status_FLG= @Cust_Active_Status_FLG
,@Dirug= @Dirug
end
hope it helps.
July 20, 2016 at 2:27 am
the insert into is working, that is not my issue, the values is the issue, i am having issues passing them, and still getting the same error :w00t:
July 20, 2016 at 4:11 am
astrid 69000 (7/20/2016)
the insert into is working, that is not my issue, the values is the issue, i am having issues passing them, and still getting the same error :w00t:
Share your SP execution script. This is the sample execution which i passed the SP and its working properly.
exec insert_data_extendedmodel_sp
@UserName= 'twin.devil'
, @CompanyName= 'Finiti'
, @IP= '255.255.255.255'
, @AP_Customer_ID= 1
, @AP_Application_ID= 10
, @BH_PastCompletedLoans_CNT= 11
, @BH_CurrentLoans_CNT= 12
, @BH_PastdueMaxNumRefuse_CNT= 13
, @BH_LegalIndex_FLG= 1
, @BH_LoanRefuse_FLG= 1
, @LO_LoanRequest_AMT= 14
, @LO_LoanObjective_CD= 15
, @LO_LoanPeriod_CNT= 16
, @LO_LoanAmortSchedule_CD= 17
, @Cust_Age_CNT= 18
, @Cust_Gender= 1
, @Cust_MaritalStatus_CD= 19
, @Cust_Num_Child_22_Below_CNT= 20
, @Cust_Education_CD= 21
, @Cust_EmploymentType_CD= 22
, @Cust_WorkType_CD= 23
, @Cust_City_CD= 24
, @Cust_Street_CD= 25
, @Cust_addressNum_CNT= 26
, @Cust_ZipCode_CNT= 27
, @Cust_EmploymentSeniority_CNT= 28
, @Fin_CustNetIncome_AMT= 29
, @Fin_HouseholdNetIncome_AMT= 30
, @Fin_ResidenceOwnership_FLG= 31
, @Fin_Residential_Lease_AMT= 32
, @Fin_Residential_Mortgage_AMT= 33
, @Fin_OtherLoansPayMonthly_AMT= 34
, @Fin_CreditCardOwnership_FLG= 35
, @FIN_FinanceInvest_CD= 36
, @COL_CollateralType_CD= 37
, @COL_SavingAccountMonthly_AMT= 38
, @COL_SavingAccount_AMT= 39
, @COL_SavingAccountLiq_AMT= 40
, @COL_SavingAccountLiq_DT= '2016-01-01'
, @COL_IshtalmutMonthly_AMT= 42
, @COL_Ishtalmut_AMT= 43
, @COL_IshtalmutLiq_AMT= 44
, @COL_IshtalmutLiq_DT= '2016-01-31'
, @Cust_BankDeposit_CD= 1
, @Cust_BranchDeposit_CD= 2
, @Cust_BankWithdraw_CD= 3
, @Cust_BranchWithdraw_CD= 4
, @Cust_Active_Status_FLG= 5
, @Dirug= 'abc'
July 20, 2016 at 9:18 pm
My question would be... if all of these tables look the same and the only difference is the company that uses them, why use separate tables? You already have CompanyName stored in the table to identify rows.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 21, 2016 at 8:12 am
I agree with Jeff, that if you have company name (or a company number integer) as part of the table, then you can just use one table and identify the company you want in your query's where clause. But I have seen a situation where identical databases were kept separate because of a contractual requirement. (One client insisted that its data not be mingled in a common database.) To permit that we had virtually identical code for each database.
If you have all the tables with slightly different names, you are going to have to use dynamic SQL for all your queries.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 24, 2016 at 11:37 pm
Hi,
sorry for the delay, i think i was having a type or something cause i did run the code again and it works now.
the issue with using a company id, is not only i am not very strong (understatement lol) on dynamic query and everything there runs on it, but also i need to write the project on c# and there i know even less hahahahah.
thanks so much!!! i will still now and try to process it all.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply