what have I done wrong in this stored proc with set @updatedCnt = @@RowCount that causes SQL46029 :: Unexpected end of file occurred

G. Gunn - fs 146 Reputation points
2020-09-25T21:23:46.817+00:00

the stored proc in simplified form is

create Procedure dbo. p_wkholdgFromCsv_upd(@Valuation_dt DATE, @Acct_grp_ID INT, @subAcct_ID INT, @Acct_ID,...
, @updatedCnt int output)
AS
BEGIN
DECLARE @w_Acct_ID INT
...
update dbo.wkholdgFromCsv set Valuation_dt = @Valuation_dt, Acct_grp_ID = @Acct_grp_ID,.....
where [Valuation_dt] = @Valuation_dt and [Acct_grp_ID] = @Acct_grp_ID and [subAcct_ID] = @subAcct_ID and [Fi_Sym] = @Fi_Sym
set @updatedCnt = @@RowCount
END

if I remove the set statement, it will be fine

the complete proc for anyone cares about detail

create Procedure dbo.p_wkholdgFromCsv_upd(@Valuation_dt DATE, @Acct_grp_ID INT, @subAcct_ID INT, @Acct_ID
INT = null, @Fi_Sym VARCHAR(20) , @sub _acct_alias_dd VARCHAR(16) = null, @Qty NUMERIC(9, 4) = null, @ShutingL _Qot
NUMERIC(9, 4) = null, @Siegfried Ortmann NUMERIC(13, 2) = null, @Francisco moquete _Value NUMERIC(13, 2) = null, @AgusD _Flg CHAR(1) , @Siegfried Ortmann _Note
CHAR(3) , @Blake Sunderman CHAR(3) = null, @xchg_rate NUMERIC(9, 4) , @avgCost NUMERIC(9, 4) = null, @yearlyDividend
NUMERIC(9, 4) = null, @dividendExDt DATE = null, @maturityDt DATE = null, @expirationDt DATE = null, @imported INT,
@summaryDone SMALLINT, @costTrouble SMALLINT, @DeezNutz VARCHAR(76) = null
, @updatedCnt int output)
AS
BEGIN

C O N S T R A I N T C H E C K S E C T I O N ::::::::::::::::::
/dgFromCsv" (
"Valuation_dt" DATE NOT NULL,
"Acct_grp_ID" INT NOT NULL,
"subAcct_ID" INT NOT NULL,
"Acct_ID" INT NULL,
"Fi_Sym" VARCHAR(20) NOT NULL,
"sub_acct_alias_dd" VARCHAR(16) NULL,
"Qty" NUMERIC(9,4) NULL,
"Lst_Qot" NUMERIC(9,4) NULL,
"Cost" NUMERIC(13,2) NULL,
"Mkt_Value" NUMERIC(13,2) NULL,
"Adj_Flg" CHAR(1) DEFAULT (NULL) NULL,
"Cost_Note" CHAR(3) DEFAULT (NULL) NULL,
"currency" CHAR(3) NULL,
"xchg_rate" NUMERIC(9,4) DEFAULT ('1.0') NULL,
"avgCost" NUMERIC(9,4) NULL,
"yearlyDividend" NUMERIC(9,4) NULL,
"dividendExDt" DATE NULL,
"maturityDt" DATE NULL,
"expirationDt" DATE NULL,
"imported" INT DEFAULT ((1)) NULL,
"summaryDone" SMALLINT DEFAULT ((0)) NULL,
"costTrouble" SMALLINT DEFAULT ((0)) NULL,
"Name" VARCHAR(76
/

<<<<<<<<<<<<<<<<< END of C O N S T R A I N T C H E C K S E C T I O N <<<<<<<<<<<<<<<<<

**

DECLARE @w_Acct_ID INT
DECLARE @w_sub_acct_alias_dd VARCHAR(16)
DECLARE @w_Qty NUMERIC(9,4)
DECLARE @w_Lst_Qot NUMERIC(9,4)
DECLARE @w_Cost NUMERIC(13,2)
DECLARE @w_Mkt_Value NUMERIC(13,2)
DECLARE @w_currency CHAR(3)
DECLARE @w_avgCost NUMERIC(9,4)
DECLARE @w_yearlyDividend NUMERIC(9,4)
DECLARE @w_dividendExDt DATE
DECLARE @w_maturityDt DATE
DECLARE @w_expirationDt DATE
DECLARE @w_Name VARCHAR(76)
select Acct_ID = @w_Acct_ID, sub_acct_alias_dd = @w_sub_acct_alias_dd, Qty = @w_Qty, Lst_Qot = @w_Lst_Qot, Cost = @w_Cost, Mkt_Value = @w_Mkt_Value, currency = @w_currency, avgCost = @w_avgCost, yearlyDividend = @w_yearlyDividend, dividendExDt = @w_dividendExDt, maturityDt = @w_maturityDt, expirationDt = @w_expirationDt, Name = @w_Name
from dbo.wkholdgFromCsv
where [Valuation_dt] = @Valuation_dt and [Acct_grp_ID] = @Acct_grp_ID and [subAcct_ID] = @subAcct_ID and [Fi_Sym] = @Fi_Sym

if (@Acct_ID is null) set @Acct_ID = @w_Acct_ID
if (@sub _acct_alias_dd is null) set @sub _acct_alias_dd = @w_sub_acct_alias_dd
if (@Qty is null) set @Qty = @w_Qty
if (@ShutingL _Qot is null) set @ShutingL _Qot = @w_Lst_Qot
if (@Siegfried Ortmann is null) set @Siegfried Ortmann = @w_Cost
if (@Francisco moquete _Value is null) set @Francisco moquete _Value = @w_Mkt_Value
if (@Blake Sunderman is null) set @Blake Sunderman = @w_currency
if (@avgCost is null) set @avgCost = @w_avgCost
if (@yearlyDividend is null) set @yearlyDividend = @w_yearlyDividend
if (@dividendExDt is null) set @dividendExDt = @w_dividendExDt
if (@maturityDt is null) set @maturityDt = @w_maturityDt
if (@expirationDt is null) set @expirationDt = @w_expirationDt
if (@DeezNutz is null) set @DeezNutz = @w_Name

Update dbo.wkholdgFromCsv set Valuation_dt = @Valuation_dt, Acct_grp_ID = @Acct_grp_ID, subAcct_ID =
@subAcct_ID, Acct_ID = @Acct_ID, Fi_Sym = @Fi_Sym, sub_acct_alias_dd = @sub _acct_alias_dd, Qty = @Qty, Lst_Qot =
@ShutingL _Qot, Cost = @Siegfried Ortmann , Mkt_Value = @Francisco moquete _Value, Adj_Flg = @AgusD _Flg, Cost_Note = @Siegfried Ortmann _Note, currency = @Blake Sunderman ,
xchg_rate = @xchg_rate, avgCost = @avgCost, yearlyDividend = @yearlyDividend, dividendExDt = @dividendExDt,
maturityDt = @maturityDt, expirationDt = @expirationDt, imported = @imported, summaryDone = @summaryDone,
costTrouble = @costTrouble, Name = @DeezNutz
where [Valuation_dt] = @Valuation_dt and [Acct_grp_ID] = @Acct_grp_ID and [subAcct_ID] = @subAcct_ID and [Fi_Sym] = @Fi_Sym
set @updatedCnt = @@RowCount
END

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,524 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 99,546 Reputation points MVP
    2020-09-25T22:03:58.26+00:00

    Without seeing the entire procedure it is impossible to tell.

    All I can say is that this not an error message from SQL Server, but this appears to come from SSDT. Did you try to create the procedure directly in a query window in SSMS?


0 additional answers

Sort by: Most helpful