July 6, 2017 at 8:24 pm
Here is procedure I am trying to create:
ALTER PROCEDURE p_UpdateBorrowerNote
-- Add the parameters for the stored procedure here
@loanList varchar(max),
@letter varchar(50),
@firstName char(16)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @LetterNote varchar(200)
DECLARE @TimeStamp datetime
DECLARE @Operator char(16)
DECLARE @LoanTable table(LoanNumber char(11))
DECLARE @ApplTable table(xref_xappl_id int not null,
ext_appl char(16) not null,
ext_appl_account char(50) not null,
added_dtm datetime not null,
added_operator char(16) not null,
update_operator char(16) not null,
update_dtm datetime not null)
DECLARE @BaseTable table(history_id bigint not null,
base_table varchar(4) null,
base_id int not null,
eff_date datetime null,
posting_dt datetime null,
source_cd char(8) not null,
tran_cd char(8) not null,
added_dtm datetime not null,
added_operator char(16) not null,
update_operator char(16) not null,
update_dtm datetime not null)
set @LetterNote = @letter + ' sent on ' + convert(varchar(10), convert(date, GetDate())) + '.' + CHAR(13)+CHAR(10)
set @TimeStamp = GetDate()
set @Operator = IIF(@firstName is null, 'Letter',@firstName)
insert @LoanTable Select Item as LoanNumber From dbo.f_ListToTable(@loanList,',') order by item
INSERT INTO OpsWBComments.dbo.WX01_XREF_XAPPL (Ext_appl, Ext_appl_Account, Added_Operator, Update_Operator)
OUTPUT INSERTED.Ext_appl,
INSERTED.Ext_appl_Account,
INSERTED.Added_Operator,
INSERTED.Update_Operator
INTO @ApplTable
SELECT 'COMMERCIAL' as Ext_appl, '000100010'+lt.LoanNumber as Ext_appl_Account, @Operator as Added_Operator, @Operator as Update_Operator
FROM @LoanTable lt
ORDER BY LoanNumber
INSERT INTO OpsWBComments.dbo.WH01_HIST_BASE (Base_Table, Base_ID, Eff_dt, Posting_Dt, Source_cd, Tran_cd, Added_Operator, Update_Operator)
OUTPUT INSERTED.Base_Table,
INSERTED.Base_ID,
INSERTED.Eff_dt,
INSERTED.Posting_Dt,
INSERTED.Source_cd,
INSERTED.Tran_cd,
INSERTED.Added_Operator,
INSERTED.Update_Operator
INTO @BaseTable
SELECT Base_Table = 'WX01', Base_ID = at.Xref_Xappl_ID, Eff_dt = @Timestamp, Posting_dt = @TimeStamp, Source_cd = 'SSRSLET', Tran_cd = 'CMTADD',
Added_Operator = @Operator, Update_Operator = @Operator
FROM @ApplTable at
ORDER BY Xref_Xappl_ID
INSERT INTO OpsWBComments.dbo.WH05_HIST_COMNT (History_ID, Tied_Table, Tied_ID, Reason_cd, [Text], Type_cd)
SELECT bt.History_ID, Tied_Table = 'WX01', Tied_ID = ht.Base_ID, Reason_cd = 'LtLetter', [Text] = @LetterNote, Type_cd = 'COLLECTI'
FROM @BaseTable bt
END
GO
I am getting this error when trying to Alter the procedure and the error takes me to this line :INSERT INTO OpsWBComments.dbo.WX01_XREF_XAPPL (Ext_appl, Ext_appl_Account, Added_Operator, Update_Operator)
Msg 213, Level 16, State 1, Procedure p_UpdateBorrowerNote, Line 62
Column name or number of supplied values does not match table definition.
In the table OpsWBComments.dbo.WX01_XREF_XAPPL the column xref_xappl_id is an identity column and the columns added_dtm and update_dtm are both defaulted to GetDate()
According to the specs on Using OUTPUT the identity and defaulted columns should not be included?
Any help would be greatly appreciated.
July 7, 2017 at 2:39 am
The error is giving the game away here, you haven'tt supplied enough columns. Your table @BaseTable has 11 columns, however, your OUTPUT INTO only has 8. You need to supply 11 columns, or declare which columns you are inserting into.
Perhaps replace your INTO statement with:INTO @BaseTable (Base_table, Base_id, Eff_dt, Posting_dt, Source_cd, Tran_cd, Added_operator, Update_operator)
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 7, 2017 at 10:33 am
Thank you for your reply but.....
This is the example right out of Microsoft's specifications. They have created a table variable with 5 fields and only specify 3 in the insert. The specs say to NOT include an identity field which is what the first field in the table definition is.
.
USE AdventureWorks2012 ;
GO
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( EmployeeID int IDENTITY (1,5)NOT NULL,
LastName nvarchar(20) NOT NULL,
FirstName nvarchar(20) NOT NULL,
CurrentSales money NOT NULL,
ProjectedSales AS CurrentSales * 1.10
);
GO
DECLARE @MyTableVar table(
EmployeeID int NOT NULL,
LastName nvarchar(20) NOT NULL,
FirstName nvarchar(20) NOT NULL,
CurrentSales money NOT NULL,
ProjectedSales money NOT NULL
);
INSERT INTO dbo.EmployeeSales (LastName, FirstName, CurrentSales)
OUTPUT INSERTED.LastName,
INSERTED.FirstName,
INSERTED.CurrentSales
INTO @MyTableVar
SELECT c.LastName, c.FirstName, sp.SalesYTD
FROM Sales.SalesPerson AS sp
INNER JOIN Person.Person AS c
ON sp.BusinessEntityID = c.BusinessEntityID
WHERE sp.BusinessEntityID LIKE '2%'
ORDER BY c.LastName, c.FirstName;
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
FROM @MyTableVar;
GO
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
FROM dbo.EmployeeSales;
GO
July 7, 2017 at 11:10 am
And their code is wrong for the same reason. I get the same error running the code you posted from MS as your own code received. The problem is still that you haven't provided the same number of columns as in the table @ApplTable.
You either need to make the columns you are not inserting data into nullable or provide defaults for the columns you are not specifying.
July 7, 2017 at 11:15 am
Here is the MS code modified to work:
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( EmployeeID int IDENTITY (1,5)NOT NULL,
LastName nvarchar(20) NOT NULL,
FirstName nvarchar(20) NOT NULL,
CurrentSales money NOT NULL,
ProjectedSales AS CurrentSales * 1.10
);
GO
DECLARE @MyTableVar table(
EmployeeID int NOT NULL,
LastName nvarchar(20) NOT NULL,
FirstName nvarchar(20) NOT NULL,
CurrentSales money NOT NULL,
ProjectedSales money NOT NULL
);
INSERT INTO dbo.EmployeeSales (LastName, FirstName, CurrentSales)
OUTPUT
INSERTED.[EmployeeID],
INSERTED.LastName,
INSERTED.FirstName,
INSERTED.CurrentSales,
INSERTED.[ProjectedSales]
INTO @MyTableVar
SELECT c.LastName, c.FirstName, sp.SalesYTD
FROM Sales.SalesPerson AS sp
INNER JOIN Person.Person AS c
ON sp.BusinessEntityID = c.BusinessEntityID
WHERE sp.BusinessEntityID LIKE '2%'
ORDER BY c.LastName, c.FirstName;
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
FROM @MyTableVar;
GO
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
FROM dbo.EmployeeSales;
GO
July 7, 2017 at 11:30 am
And the INSERTED.EmployeeID would have the next identity value that is added to the Employee table in it's insert?
July 7, 2017 at 11:49 am
john.eder - Friday, July 7, 2017 11:30 AMAnd the INSERTED.EmployeeID would have the next identity value that is added to the Employee table in it's insert?
Try it and see.
July 7, 2017 at 12:09 pm
Nice answer.
Thanks for your help.
July 7, 2017 at 12:45 pm
john.eder - Friday, July 7, 2017 12:09 PMNice answer.Thanks for your help.
Best way to learn is to do it, not always rely on what others say. That is why you should have a sandbox database, to try things you aren't sure about. Nothing wrong with asking for help, it provides you with a variety of options. At some point, however, you need to actually test things out and see how they work (or don't work).
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply