Hi all,
I had a stored procedure that i wanted to be able to pass parameters
to so i used dynamic SQL. I am getting very strange errors though when
i try to execute it. My code works just fine, but when i add the
dynamic sql parts in, it gives the strangest errors. Here is the code
that executes it...
Declare @.data_source varchar(50)
Declare @.target_DB varchar(20)
Declare @.trans_start varchar(8)
Declare @.trans_stop varchar(8)
SET @.data_source = 'vwBST_Expense_Transfer_ODCs'
SET @.target_DB = 'DevDB'
SET @.trans_start = '09/26/55'
SET @.trans_stop = '10/23/04'
exec spBST_RSB_Import_Expense_Transfer_ODCs @.target_DB, @.data_source,
@.trans_start, @.trans_stop
And here is the code that is in the sp...
CREATE PROCEDURE spBST_RSB_Import_Expense_Transfer_ODCs
@.target_DB varchar(20),
@.data_source varchar(50),
@.trans_start varchar(8),
@.trans_stop varchar(8)
AS
Declare @.string_procedure varchar(8000)
SET @.string_procedure = '
DECLARE @.usr_doc_type_suf varchar(2),
@.acct_code varchar(10),
@.prj_code varchar(10),
@.phase_code varchar(6),
@.task_code varchar(6),
@.org_code varchar(8),
@.prj_cost numeric(13),
@.co_cost numeric(13),
@.acct_cost numeric(13),
@.prj_eff numeric(13),
@.co_eff numeric(13),
@.per_end_date datetime,
@.doc_nbr varchar(10),
@.doc_date datetime,
@.trn_date datetime,
@.evc_type_code char(1),
@.evc_code varchar(9),
@.extern_ref_nbr varchar(15),
@.extern_ref_date datetime,
@.intern_ref_nbr varchar(10),
@.rsrc_code varchar(6),
@.pcs_desc varchar(255),
@.batch_skey varchar(4),
@.co_code varchar(4),
@.usr_batch_id varchar(3),
@.sys_doc_type_code varchar(2),
@.max_dcml_places smallint,
@.input_date datetime,
@.batch_doc_total smallint,
@.batch_prj_cost_total numeric(13),
@.batch_prj_eff_total numeric(13),
@.batch_co_eff_total numeric(13),
--seq_nbr
@.currency_code varchar(5),
@.currency_rate numeric(13),
@.unit_input_yn char(1),
@.doc_prj_cost_total numeric(13),
@.doc_prj_eff_total numeric(13),
@.doc_co_eff_total numeric(13),
--line_nbr,
@.trn_co_code varchar(4),
@.auto_trn_yn char(1),
@.actv_code varchar(4),
@.bill_nonbill_ind char(1),
@.qty_amt float (8)
Declare @.strYN varchar(1)
Declare @.doc_nbr_hold varchar(10)
Declare @.line_count int
Declare @.seq_count int
Declare @.transaction_total numeric(13)
Declare @.project_effort numeric(13)
Declare @.batch numeric(8)
Declare @.prj_count numeric(8)
DECLARE expense_transfer_odcs_cursor CURSOR FOR
SELECT
usr_doc_type_suf,
acct_code,
prj_code,
phase_code,
task_code,
org_code,
prj_cost,
co_cost,
acct_cost,
prj_eff,
co_eff,
per_end_date,
doc_nbr,
doc_date,
trn_date,
evc_type_code,
evc_code,
extern_ref_nbr,
extern_ref_date,
intern_ref_nbr,
rsrc_code,
pcs_desc,
batch_skey,
co_code,
usr_batch_id,
sys_doc_type_code,
max_dcml_places,
input_date,
batch_doc_total,
batch_prj_cost_total,
batch_prj_eff_total,
batch_co_eff_total,
--seq_nbr,
currency_code,
currency_rate,
unit_input_yn,
doc_prj_cost_total,
doc_prj_eff_total,
doc_co_eff_total,
--line_nbr,
trn_co_code,
auto_trn_yn,
actv_code,
bill_nonbill_ind,
qty_amt
FROM ' + @.data_source + '
WHERE trn_date >= cast(''' + @.trans_start + ''' as datetime(8)) AND
trn_date <= cast(''' + @.trans_stop + ''' as datetime(8))
OPEN expense_transfer_odcs_cursor
FETCH NEXT FROM expense_transfer_odcs_cursor INTO
@.usr_doc_type_suf,
@.acct_code,
@.prj_code,
@.phase_code,
@.task_code,
@.org_code,
@.prj_cost,
@.co_cost,
@.acct_cost,
@.prj_eff,
@.co_eff,
@.per_end_date,
@.doc_nbr,
@.doc_date,
@.trn_date,
@.evc_type_code,
@.evc_code,
@.extern_ref_nbr,
@.extern_ref_date,
@.intern_ref_nbr,
@.rsrc_code,
@.pcs_desc,
@.batch_skey,
@.co_code,
@.usr_batch_id,
@.sys_doc_type_code,
@.max_dcml_places,
@.input_date,
@.batch_doc_total,
@.batch_prj_cost_total,
@.batch_prj_eff_total,
@.batch_co_eff_total,
--seq_nbr,
@.currency_code,
@.currency_rate,
@.unit_input_yn,
@.doc_prj_cost_total,
@.doc_prj_eff_total,
@.doc_co_eff_total,
--line_nbr,
@.trn_co_code,
@.auto_trn_yn,
@.actv_code,
@.bill_nonbill_ind,
@.qty_amt
SET @.doc_nbr_hold = ''-1''
SET @.seq_count = 0
SET @.line_count = 0
SET @.project_effort = 0
SET @.prj_count = 0
SET @.batch = 0
WHILE
@.@.FETCH_STATUS = 0
BEGIN
If @.prj_count = 20 AND @.prj_code <> @.doc_nbr_hold
BEGIN
SET @.seq_count = 0
SET @.batch = @.batch + 1
SET @.prj_count = 0
END
SET @.batch_skey = @.batch + 1
If @.prj_code = @.doc_nbr_hold --if project is same
BEGIN
SET @.transaction_total = @.transaction_total + @.acct_cost
SET @.line_count= @.line_count + 1 --line is
different
END
ELSE --if its a new project
(includes 1st record)
BEGIN
If @.doc_nbr_hold <> ''-1''
BEGIN
INSERT BCWCK27.' + @.target_DB + '.dbo.et_interface (--adds offset
row
usr_doc_type_suf,
acct_code,
prj_code,
phase_code,
task_code,
org_code,
prj_cost,
co_cost,
acct_cost,
prj_eff,
co_eff,
per_end_date,
doc_nbr,
doc_date,
trn_date,
evc_type_code,
evc_code,
extern_ref_nbr,
extern_ref_date,
intern_ref_nbr,
rsrc_code,
pcs_desc,
batch_skey,
co_code,
usr_batch_id,
sys_doc_type_code,
max_dcml_places,
input_date,
batch_doc_total,
batch_prj_cost_total,
batch_prj_eff_total,
batch_co_eff_total,
seq_nbr,
currency_code,
currency_rate,
unit_input_yn,
doc_prj_cost_total,
doc_prj_eff_total,
doc_co_eff_total,
line_nbr,
trn_co_code,
auto_trn_yn,
actv_code,
bill_nonbill_ind,
qty_amt)
Values( @.usr_doc_type_suf,
@.acct_code,
''000001'',
''****'',
''****'',
''1999'',
-1 * @.transaction_total,
-1 * @.transaction_total,
-1 * @.transaction_total,
-1 * @.project_effort,
-1 * @.project_effort,
@.per_end_date,
@.seq_count,
@.doc_date,
@.trn_date,
@.evc_type_code,
@.evc_code,
@.extern_ref_nbr,
@.extern_ref_date,
@.intern_ref_nbr,
@.rsrc_code,
@.pcs_desc,
CASE WHEN @.prj_count = 0 THEN cast(@.batch_skey as numeric(8)) - 1
ELSE @.batch_skey END,
@.co_code,
@.usr_batch_id,
@.sys_doc_type_code,
@.max_dcml_places,
@.input_date,
@.batch_doc_total,
@.batch_prj_cost_total,
@.batch_prj_eff_total,
@.batch_co_eff_total,
CASE WHEN @.prj_count = 0 THEN 20 ELSE @.seq_count END,
@.currency_code,
@.currency_rate,
@.unit_input_yn,
@.doc_prj_cost_total,
@.doc_prj_eff_total,
@.doc_co_eff_total,
isNull(@.line_count,0) + 1,
@.trn_co_code,
@.auto_trn_yn,
@.actv_code,
@.bill_nonbill_ind,
@.qty_amt)
SET @.line_count = @.line_count + 1
END
SET @.transaction_total = @.acct_cost
SET @.seq_count = @.seq_count + 1
SET @.line_count = 1
SET @.prj_count = @.prj_count + 1
END
INSERT BCWCK27.' + @.target_DB + '.dbo.et_interface (-- insert
regular row
usr_doc_type_suf,
acct_code,
prj_code,
phase_code,
task_code,
org_code,
prj_cost,
co_cost,
acct_cost,
prj_eff,
co_eff,
per_end_date,
doc_nbr,
doc_date,
trn_date,
evc_type_code,
evc_code,
extern_ref_nbr,
extern_ref_date,
intern_ref_nbr,
rsrc_code,
pcs_desc,
batch_skey,
co_code,
usr_batch_id,
sys_doc_type_code,
max_dcml_places,
input_date,
batch_doc_total,
batch_prj_cost_total,
batch_prj_eff_total,
batch_co_eff_total,
seq_nbr,
currency_code,
currency_rate,
unit_input_yn,
doc_prj_cost_total,
doc_prj_eff_total,
doc_co_eff_total,
line_nbr,
trn_co_code,
auto_trn_yn,
actv_code,
bill_nonbill_ind,
qty_amt)
Values( @.usr_doc_type_suf,
@.acct_code,
@.prj_code,
@.phase_code,
@.task_code,
@.org_code,
@.prj_cost,
@.co_cost,
@.acct_cost,
@.prj_eff,
@.co_eff,
@.per_end_date,
@.seq_count,
@.doc_date,
@.trn_date,
@.evc_type_code,
@.evc_code,
@.extern_ref_nbr,
@.extern_ref_date,
@.intern_ref_nbr,
@.rsrc_code,
@.pcs_desc,
@.batch_skey,
@.co_code,
@.usr_batch_id,
@.sys_doc_type_code,
@.max_dcml_places,
@.input_date,
@.batch_doc_total,
@.batch_prj_cost_total,
@.batch_prj_eff_total,
@.batch_co_eff_total,
@.seq_count,
@.currency_code,
@.currency_rate,
@.unit_input_yn,
@.doc_prj_cost_total,
@.doc_prj_eff_total,
@.doc_co_eff_total,
@.line_count,
@.trn_co_code,
@.auto_trn_yn,
@.actv_code,
@.bill_nonbill_ind,
@.qty_amt)
/*Update BCWCK27.DevDB.dbo.vchr_interface
SET doc_trn_cr_total = @.transaction_total, doc_acct_cr_total =
@.transaction_total, doc_co_cr_total = @.transaction_total
WHERE doc_nbr = @.doc_nbr*/
SET @.doc_nbr_hold = @.prj_code
FETCH NEXT FROM expense_transfer_odcs_cursor INTO
@.usr_doc_type_suf,
@.acct_code,
@.prj_code,
@.phase_code,
@.task_code,
@.org_code,
@.prj_cost,
@.co_cost,
@.acct_cost,
@.prj_eff,
@.co_eff,
@.per_end_date,
@.doc_nbr,
@.doc_date,
@.trn_date,
@.evc_type_code,
@.evc_code,
@.extern_ref_nbr,
@.extern_ref_date,
@.intern_ref_nbr,
@.rsrc_code,
@.pcs_desc,
@.batch_skey,
@.co_code,
@.usr_batch_id,
@.sys_doc_type_code,
@.max_dcml_places,
@.input_date,
@.batch_doc_total,
@.batch_prj_cost_total,
@.batch_prj_eff_total,
@.batch_co_eff_total,
--seq_nbr,
@.currency_code,
@.currency_rate,
@.unit_input_yn,
@.doc_prj_cost_total,
@.doc_prj_eff_total,
@.doc_co_eff_total,
--line_nbr,
@.trn_co_code,
@.auto_trn_yn,
@.actv_code,
@.bill_nonbill_ind,
@.qty_amt
END
INSERT BCWCK27.' + @.target_DB + '.dbo.et_interface (--adds offset
row
usr_doc_type_suf,
acct_code,
prj_code,
phase_code,
task_code,
org_code,
prj_cost,
co_cost,
acct_cost,
prj_eff,
co_eff,
per_end_date,
doc_nbr,
doc_date,
trn_date,
evc_type_code,
evc_code,
extern_ref_nbr,
extern_ref_date,
intern_ref_nbr,
rsrc_code,
pcs_desc,
batch_skey,
co_code,
usr_batch_id,
sys_doc_type_code,
max_dcml_places,
input_date,
batch_doc_total,
batch_prj_cost_total,
batch_prj_eff_total,
batch_co_eff_total,
seq_nbr,
currency_code,
currency_rate,
unit_input_yn,
doc_prj_cost_total,
doc_prj_eff_total,
doc_co_eff_total,
line_nbr,
trn_co_code,
auto_trn_yn,
actv_code,
bill_nonbill_ind,
qty_amt)
Values( @.usr_doc_type_suf,
@.acct_code,
''000001'',
''****'',
''****'',
''1999'',
-1 * @.transaction_total,
-1 * @.transaction_total,
-1 * @.transaction_total,
-1 * @.project_effort,
-1 * @.project_effort,
@.per_end_date,
@.seq_count,
@.doc_date,
@.trn_date,
@.evc_type_code,
@.evc_code,
@.extern_ref_nbr,
@.extern_ref_date,
@.intern_ref_nbr,
@.rsrc_code,
@.pcs_desc,
@.batch_skey,
@.co_code,
@.usr_batch_id,
@.sys_doc_type_code,
@.max_dcml_places,
@.input_date,
@.batch_doc_total,
@.batch_prj_cost_total,
@.batch_prj_eff_total,
@.batch_co_eff_total,
@.seq_count,
@.currency_code,
@.currency_rate,
@.unit_input_yn,
@.doc_prj_cost_total,
@.doc_prj_eff_total,
@.doc_co_eff_total,
isNull(@.line_count,0) + 1,
@.trn_co_code,
@.auto_trn_yn,
@.actv_code,
@.bill_nonbill_ind,
@.qty_amt)
CLOSE expense_transfer_odcs_cursor
DEALLOCATE expense_transfer_odcs_cursor
'
Exec (@.string_procedure)
GOHi
Although you say it errors you don't say what they are. If you ran profiler
you could see the statement sent to the server, alternatively you can print
the statement instead of executing it.
John
"Richard" <rdeslonde@.hotmail.com> wrote in message
news:d3191883.0407011302.63bfa804@.posting.google.c om...
> Hi all,
> I had a stored procedure that i wanted to be able to pass parameters
> to so i used dynamic SQL. I am getting very strange errors though when
> i try to execute it. My code works just fine, but when i add the
> dynamic sql parts in, it gives the strangest errors. Here is the code
> that executes it...
>
> Declare @.data_source varchar(50)
> Declare @.target_DB varchar(20)
> Declare @.trans_start varchar(8)
> Declare @.trans_stop varchar(8)
> SET @.data_source = 'vwBST_Expense_Transfer_ODCs'
> SET @.target_DB = 'DevDB'
> SET @.trans_start = '09/26/55'
> SET @.trans_stop = '10/23/04'
> exec spBST_RSB_Import_Expense_Transfer_ODCs @.target_DB, @.data_source,
> @.trans_start, @.trans_stop
>
> And here is the code that is in the sp...
> CREATE PROCEDURE spBST_RSB_Import_Expense_Transfer_ODCs
>
> @.target_DB varchar(20),
> @.data_source varchar(50),
> @.trans_start varchar(8),
> @.trans_stop varchar(8)
> AS
> Declare @.string_procedure varchar(8000)
> SET @.string_procedure = '
>
> DECLARE @.usr_doc_type_suf varchar(2),
> @.acct_code varchar(10),
> @.prj_code varchar(10),
> @.phase_code varchar(6),
> @.task_code varchar(6),
> @.org_code varchar(8),
> @.prj_cost numeric(13),
> @.co_cost numeric(13),
> @.acct_cost numeric(13),
> @.prj_eff numeric(13),
> @.co_eff numeric(13),
> @.per_end_date datetime,
> @.doc_nbr varchar(10),
> @.doc_date datetime,
> @.trn_date datetime,
> @.evc_type_code char(1),
> @.evc_code varchar(9),
> @.extern_ref_nbr varchar(15),
> @.extern_ref_date datetime,
> @.intern_ref_nbr varchar(10),
> @.rsrc_code varchar(6),
> @.pcs_desc varchar(255),
> @.batch_skey varchar(4),
> @.co_code varchar(4),
> @.usr_batch_id varchar(3),
> @.sys_doc_type_code varchar(2),
> @.max_dcml_places smallint,
> @.input_date datetime,
> @.batch_doc_total smallint,
> @.batch_prj_cost_total numeric(13),
> @.batch_prj_eff_total numeric(13),
> @.batch_co_eff_total numeric(13),
> --seq_nbr
> @.currency_code varchar(5),
> @.currency_rate numeric(13),
> @.unit_input_yn char(1),
> @.doc_prj_cost_total numeric(13),
> @.doc_prj_eff_total numeric(13),
> @.doc_co_eff_total numeric(13),
> --line_nbr,
> @.trn_co_code varchar(4),
> @.auto_trn_yn char(1),
> @.actv_code varchar(4),
> @.bill_nonbill_ind char(1),
> @.qty_amt float (8)
> Declare @.strYN varchar(1)
> Declare @.doc_nbr_hold varchar(10)
> Declare @.line_count int
> Declare @.seq_count int
> Declare @.transaction_total numeric(13)
> Declare @.project_effort numeric(13)
> Declare @.batch numeric(8)
> Declare @.prj_count numeric(8)
>
> DECLARE expense_transfer_odcs_cursor CURSOR FOR
> SELECT
> usr_doc_type_suf,
> acct_code,
> prj_code,
> phase_code,
> task_code,
> org_code,
> prj_cost,
> co_cost,
> acct_cost,
> prj_eff,
> co_eff,
> per_end_date,
> doc_nbr,
> doc_date,
> trn_date,
> evc_type_code,
> evc_code,
> extern_ref_nbr,
> extern_ref_date,
> intern_ref_nbr,
> rsrc_code,
> pcs_desc,
> batch_skey,
> co_code,
> usr_batch_id,
> sys_doc_type_code,
> max_dcml_places,
> input_date,
> batch_doc_total,
> batch_prj_cost_total,
> batch_prj_eff_total,
> batch_co_eff_total,
> --seq_nbr,
> currency_code,
> currency_rate,
> unit_input_yn,
> doc_prj_cost_total,
> doc_prj_eff_total,
> doc_co_eff_total,
> --line_nbr,
> trn_co_code,
> auto_trn_yn,
> actv_code,
> bill_nonbill_ind,
> qty_amt
> FROM ' + @.data_source + '
> WHERE trn_date >= cast(''' + @.trans_start + ''' as datetime(8)) AND
> trn_date <= cast(''' + @.trans_stop + ''' as datetime(8))
> OPEN expense_transfer_odcs_cursor
> FETCH NEXT FROM expense_transfer_odcs_cursor INTO
> @.usr_doc_type_suf,
> @.acct_code,
> @.prj_code,
> @.phase_code,
> @.task_code,
> @.org_code,
> @.prj_cost,
> @.co_cost,
> @.acct_cost,
> @.prj_eff,
> @.co_eff,
> @.per_end_date,
> @.doc_nbr,
> @.doc_date,
> @.trn_date,
> @.evc_type_code,
> @.evc_code,
> @.extern_ref_nbr,
> @.extern_ref_date,
> @.intern_ref_nbr,
> @.rsrc_code,
> @.pcs_desc,
> @.batch_skey,
> @.co_code,
> @.usr_batch_id,
> @.sys_doc_type_code,
> @.max_dcml_places,
> @.input_date,
> @.batch_doc_total,
> @.batch_prj_cost_total,
> @.batch_prj_eff_total,
> @.batch_co_eff_total,
> --seq_nbr,
> @.currency_code,
> @.currency_rate,
> @.unit_input_yn,
> @.doc_prj_cost_total,
> @.doc_prj_eff_total,
> @.doc_co_eff_total,
> --line_nbr,
> @.trn_co_code,
> @.auto_trn_yn,
> @.actv_code,
> @.bill_nonbill_ind,
> @.qty_amt
> SET @.doc_nbr_hold = ''-1''
> SET @.seq_count = 0
> SET @.line_count = 0
> SET @.project_effort = 0
> SET @.prj_count = 0
> SET @.batch = 0
>
> WHILE
> @.@.FETCH_STATUS = 0
> BEGIN
> If @.prj_count = 20 AND @.prj_code <> @.doc_nbr_hold
> BEGIN
> SET @.seq_count = 0
> SET @.batch = @.batch + 1
> SET @.prj_count = 0
> END
> SET @.batch_skey = @.batch + 1
> If @.prj_code = @.doc_nbr_hold --if project is same
> BEGIN
> SET @.transaction_total = @.transaction_total + @.acct_cost
> SET @.line_count= @.line_count + 1 --line is
> different
> END
> ELSE --if its a new project
> (includes 1st record)
> BEGIN
>
> If @.doc_nbr_hold <> ''-1''
> BEGIN
> INSERT BCWCK27.' + @.target_DB + '.dbo.et_interface ( --adds offset
> row
> usr_doc_type_suf,
> acct_code,
> prj_code,
> phase_code,
> task_code,
> org_code,
> prj_cost,
> co_cost,
> acct_cost,
> prj_eff,
> co_eff,
> per_end_date,
> doc_nbr,
> doc_date,
> trn_date,
> evc_type_code,
> evc_code,
> extern_ref_nbr,
> extern_ref_date,
> intern_ref_nbr,
> rsrc_code,
> pcs_desc,
> batch_skey,
> co_code,
> usr_batch_id,
> sys_doc_type_code,
> max_dcml_places,
> input_date,
> batch_doc_total,
> batch_prj_cost_total,
> batch_prj_eff_total,
> batch_co_eff_total,
> seq_nbr,
> currency_code,
> currency_rate,
> unit_input_yn,
> doc_prj_cost_total,
> doc_prj_eff_total,
> doc_co_eff_total,
> line_nbr,
> trn_co_code,
> auto_trn_yn,
> actv_code,
> bill_nonbill_ind,
> qty_amt)
> Values( @.usr_doc_type_suf,
> @.acct_code,
> ''000001'',
> ''****'',
> ''****'',
> ''1999'',
> -1 * @.transaction_total,
> -1 * @.transaction_total,
> -1 * @.transaction_total,
> -1 * @.project_effort,
> -1 * @.project_effort,
> @.per_end_date,
> @.seq_count,
> @.doc_date,
> @.trn_date,
> @.evc_type_code,
> @.evc_code,
> @.extern_ref_nbr,
> @.extern_ref_date,
> @.intern_ref_nbr,
> @.rsrc_code,
> @.pcs_desc,
> CASE WHEN @.prj_count = 0 THEN cast(@.batch_skey as numeric(8)) - 1
> ELSE @.batch_skey END,
> @.co_code,
> @.usr_batch_id,
> @.sys_doc_type_code,
> @.max_dcml_places,
> @.input_date,
> @.batch_doc_total,
> @.batch_prj_cost_total,
> @.batch_prj_eff_total,
> @.batch_co_eff_total,
> CASE WHEN @.prj_count = 0 THEN 20 ELSE @.seq_count END,
> @.currency_code,
> @.currency_rate,
> @.unit_input_yn,
> @.doc_prj_cost_total,
> @.doc_prj_eff_total,
> @.doc_co_eff_total,
> isNull(@.line_count,0) + 1,
> @.trn_co_code,
> @.auto_trn_yn,
> @.actv_code,
> @.bill_nonbill_ind,
> @.qty_amt)
> SET @.line_count = @.line_count + 1
>
> END
> SET @.transaction_total = @.acct_cost
> SET @.seq_count = @.seq_count + 1
> SET @.line_count = 1
> SET @.prj_count = @.prj_count + 1
> END
> INSERT BCWCK27.' + @.target_DB + '.dbo.et_interface ( -- insert
> regular row
> usr_doc_type_suf,
> acct_code,
> prj_code,
> phase_code,
> task_code,
> org_code,
> prj_cost,
> co_cost,
> acct_cost,
> prj_eff,
> co_eff,
> per_end_date,
> doc_nbr,
> doc_date,
> trn_date,
> evc_type_code,
> evc_code,
> extern_ref_nbr,
> extern_ref_date,
> intern_ref_nbr,
> rsrc_code,
> pcs_desc,
> batch_skey,
> co_code,
> usr_batch_id,
> sys_doc_type_code,
> max_dcml_places,
> input_date,
> batch_doc_total,
> batch_prj_cost_total,
> batch_prj_eff_total,
> batch_co_eff_total,
> seq_nbr,
> currency_code,
> currency_rate,
> unit_input_yn,
> doc_prj_cost_total,
> doc_prj_eff_total,
> doc_co_eff_total,
> line_nbr,
> trn_co_code,
> auto_trn_yn,
> actv_code,
> bill_nonbill_ind,
> qty_amt)
> Values( @.usr_doc_type_suf,
> @.acct_code,
> @.prj_code,
> @.phase_code,
> @.task_code,
> @.org_code,
> @.prj_cost,
> @.co_cost,
> @.acct_cost,
> @.prj_eff,
> @.co_eff,
> @.per_end_date,
> @.seq_count,
> @.doc_date,
> @.trn_date,
> @.evc_type_code,
> @.evc_code,
> @.extern_ref_nbr,
> @.extern_ref_date,
> @.intern_ref_nbr,
> @.rsrc_code,
> @.pcs_desc,
> @.batch_skey,
> @.co_code,
> @.usr_batch_id,
> @.sys_doc_type_code,
> @.max_dcml_places,
> @.input_date,
> @.batch_doc_total,
> @.batch_prj_cost_total,
> @.batch_prj_eff_total,
> @.batch_co_eff_total,
> @.seq_count,
> @.currency_code,
> @.currency_rate,
> @.unit_input_yn,
> @.doc_prj_cost_total,
> @.doc_prj_eff_total,
> @.doc_co_eff_total,
> @.line_count,
> @.trn_co_code,
> @.auto_trn_yn,
> @.actv_code,
> @.bill_nonbill_ind,
> @.qty_amt)
> /*Update BCWCK27.DevDB.dbo.vchr_interface
> SET doc_trn_cr_total = @.transaction_total, doc_acct_cr_total =
> @.transaction_total, doc_co_cr_total = @.transaction_total
> WHERE doc_nbr = @.doc_nbr*/
> SET @.doc_nbr_hold = @.prj_code
> FETCH NEXT FROM expense_transfer_odcs_cursor INTO
> @.usr_doc_type_suf,
> @.acct_code,
> @.prj_code,
> @.phase_code,
> @.task_code,
> @.org_code,
> @.prj_cost,
> @.co_cost,
> @.acct_cost,
> @.prj_eff,
> @.co_eff,
> @.per_end_date,
> @.doc_nbr,
> @.doc_date,
> @.trn_date,
> @.evc_type_code,
> @.evc_code,
> @.extern_ref_nbr,
> @.extern_ref_date,
> @.intern_ref_nbr,
> @.rsrc_code,
> @.pcs_desc,
> @.batch_skey,
> @.co_code,
> @.usr_batch_id,
> @.sys_doc_type_code,
> @.max_dcml_places,
> @.input_date,
> @.batch_doc_total,
> @.batch_prj_cost_total,
> @.batch_prj_eff_total,
> @.batch_co_eff_total,
> --seq_nbr,
> @.currency_code,
> @.currency_rate,
> @.unit_input_yn,
> @.doc_prj_cost_total,
> @.doc_prj_eff_total,
> @.doc_co_eff_total,
> --line_nbr,
> @.trn_co_code,
> @.auto_trn_yn,
> @.actv_code,
> @.bill_nonbill_ind,
> @.qty_amt
> END
>
> INSERT BCWCK27.' + @.target_DB + '.dbo.et_interface ( --adds offset
> row
> usr_doc_type_suf,
> acct_code,
> prj_code,
> phase_code,
> task_code,
> org_code,
> prj_cost,
> co_cost,
> acct_cost,
> prj_eff,
> co_eff,
> per_end_date,
> doc_nbr,
> doc_date,
> trn_date,
> evc_type_code,
> evc_code,
> extern_ref_nbr,
> extern_ref_date,
> intern_ref_nbr,
> rsrc_code,
> pcs_desc,
> batch_skey,
> co_code,
> usr_batch_id,
> sys_doc_type_code,
> max_dcml_places,
> input_date,
> batch_doc_total,
> batch_prj_cost_total,
> batch_prj_eff_total,
> batch_co_eff_total,
> seq_nbr,
> currency_code,
> currency_rate,
> unit_input_yn,
> doc_prj_cost_total,
> doc_prj_eff_total,
> doc_co_eff_total,
> line_nbr,
> trn_co_code,
> auto_trn_yn,
> actv_code,
> bill_nonbill_ind,
> qty_amt)
> Values( @.usr_doc_type_suf,
> @.acct_code,
> ''000001'',
> ''****'',
> ''****'',
> ''1999'',
> -1 * @.transaction_total,
> -1 * @.transaction_total,
> -1 * @.transaction_total,
> -1 * @.project_effort,
> -1 * @.project_effort,
> @.per_end_date,
> @.seq_count,
> @.doc_date,
> @.trn_date,
> @.evc_type_code,
> @.evc_code,
> @.extern_ref_nbr,
> @.extern_ref_date,
> @.intern_ref_nbr,
> @.rsrc_code,
> @.pcs_desc,
> @.batch_skey,
> @.co_code,
> @.usr_batch_id,
> @.sys_doc_type_code,
> @.max_dcml_places,
> @.input_date,
> @.batch_doc_total,
> @.batch_prj_cost_total,
> @.batch_prj_eff_total,
> @.batch_co_eff_total,
> @.seq_count,
> @.currency_code,
> @.currency_rate,
> @.unit_input_yn,
> @.doc_prj_cost_total,
> @.doc_prj_eff_total,
> @.doc_co_eff_total,
> isNull(@.line_count,0) + 1,
> @.trn_co_code,
> @.auto_trn_yn,
> @.actv_code,
> @.bill_nonbill_ind,
> @.qty_amt)
> CLOSE expense_transfer_odcs_cursor
> DEALLOCATE expense_transfer_odcs_cursor
> '
> Exec (@.string_procedure)
> GO|||Richard (rdeslonde@.hotmail.com) writes:
> I had a stored procedure that i wanted to be able to pass parameters
> to so i used dynamic SQL. I am getting very strange errors though when
> i try to execute it. My code works just fine, but when i add the
> dynamic sql parts in, it gives the strangest errors. Here is the code
> that executes it...
So strange that you don't even have to guts to post them?
Sure, you included the code, so I could try it myself. The first errors
were due to wrapped comments, certainly accidents in news transport.
Once those were fixed, I got:
Server: Msg 137, Level 15, State 1, Line 443
Must declare the variable '@.sys_doc_ty'.
Since your procedure is over 500 lines long, and this is a truncated
name, it appears obviously clear what happens: the dynamic SQL is
more than 8000 chars long. You could have to use two variables, and
then run it as
EXEC(@.sql1 + @.sql2)
You can also shorten the code, by writing the cursor loop as:
DECLARE mycur CURSOR FOR
SELECT ...
OPEN mycur
WHILE 1 = 1
BEGIN
FETCH mycur INTO @.var1, ...
IF @.@.fetch_status <> 0
BREAK
-- work goes here.
END
DEALLOCATE mycur
With the terrifying number of columns you have this is almost a necessity,
to reduce the risk that the two FETCH statements are different.
You may still have to two SQL variables though.
I also like to point out that with some effort you could probably get
rid of the cursor and run everything as
INSERT tbl
SELECT ...
This can give a huge boost in performance.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
