Dynamic SQL Command: errors

Showing posts with label errors. Show all posts

Sunday, February 19, 2012

Dynamic SQL trouble

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)

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,

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,

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,

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,

@.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

Friday, February 17, 2012

Dynamic SQL Question

I have to get a count of records using dynamic sql.
I have the following and I'm getting errors (Syntax error converting
the varchar value 'SELECT ' to a column of data type int.):
DECLARE @.sCalldate varchar(10)
DECLARE @.SQL varchar(8000)
DECLARE @.RC int
SELECT @.sCalldate ='20050712'
SELECT @.sEmployeeNameId = '0'
SELECT @.sAgentid ='0'
SELECT @.SQL = 'SELECT ' + @.RC + ' = Count(*)
FROM dbo.tmPunchTime
WHERE scalldate = ' + @.sCallDate +
' AND ISNULL(sRawLogout, ''x'') = ''x'''
EXEC (@.SQL)
How can I make this work?
Thanks,
Ninelhi ninel,
Please, post DDL instructions but at first, try to sustitute this line for
the another one:

> SELECT @.SQL = 'SELECT ' + @.RC + ' = Count(*)
> FROM dbo.tmPunchTime
> WHERE scalldate = ' + @.sCallDate +
> ' AND ISNULL(sRawLogout, ''x'') = ''x'''
> EXEC (@.SQL)

> set @.sql = ('
SELECT ' + @.RC + ' = Count(*)
> FROM dbo.tmPunchTime
> WHERE scalldate = ' + @.sCallDate +
> ' AND ISNULL(sRawLogout, ''x'') = ''x'''')

> EXEC (@.SQL)
"ninel" wrote:

> I have to get a count of records using dynamic sql.
> I have the following and I'm getting errors (Syntax error converting
> the varchar value 'SELECT ' to a column of data type int.):
> DECLARE @.sCalldate varchar(10)
> DECLARE @.SQL varchar(8000)
> DECLARE @.RC int
> SELECT @.sCalldate ='20050712'
> SELECT @.sEmployeeNameId = '0'
> SELECT @.sAgentid ='0'
> SELECT @.SQL = 'SELECT ' + @.RC + ' = Count(*)
> FROM dbo.tmPunchTime
> WHERE scalldate = ' + @.sCallDate +
> ' AND ISNULL(sRawLogout, ''x'') = ''x'''
> EXEC (@.SQL)
> How can I make this work?
> Thanks,
> Ninel
>|||I see no need for dynamic SQL here:
DECLARE @.sCalldate varchar(10)
DECLARE @.RC int
SELECT @.sCalldate ='20050712'
SELECT @.RC=Count(*)
FROM dbo.tmPunchTime
WHERE scalldate = @.sCallDate
AND ISNULL(sRawLogout, 'x') = 'x'
Razvan|||Hi!
declare
@.sql varchar(8000),
@.sCalidate varchar(10),
@.RC int;
set @.sql = 'select @.RC = count(*) from from tmPunchTime where scalldate =
@.sCallDate and is null(sRawLogout, ''x'') = ''x''';
exec sp_executesql @.sql, N'@.RC int out, @.sCalidate varchar(10)', @.RS out,
@.sCalidate = @.sCalidate;
select @.RC
Micle.
"ninel" <ngorbunov@.onetouchdirect-dot-com.no-spam.invalid> wrote in message
news:orOdnbUkTPNtF33fRVn_vA@.giganews.com...
>I have to get a count of records using dynamic sql.
> I have the following and I'm getting errors (Syntax error converting
> the varchar value 'SELECT ' to a column of data type int.):
> DECLARE @.sCalldate varchar(10)
> DECLARE @.SQL varchar(8000)
> DECLARE @.RC int
> SELECT @.sCalldate ='20050712'
> SELECT @.sEmployeeNameId = '0'
> SELECT @.sAgentid ='0'
> SELECT @.SQL = 'SELECT ' + @.RC + ' = Count(*)
> FROM dbo.tmPunchTime
> WHERE scalldate = ' + @.sCallDate +
> ' AND ISNULL(sRawLogout, ''x'') = ''x'''
> EXEC (@.SQL)
> How can I make this work?
> Thanks,
> Ninel
>|||Hi Ninel,
I agree with Razvan Socol that there is no need for Dynamic SQL.
Please visit this link and view When not to use Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html ( An authority on Dynamic
SQL)
If in your case you donot know TableName or Parameters are changing
then Micle approach is what you need
Please let me know if it clear your doubts.
With warm regards
Jatinder|||No need to use dynmaic sql to get the results you want. Please see example
below.
DECLARE @.sCalldate varchar(10)
DECLARE @.SQL varchar(8000)
DECLARE @.RC int
DECLARE @.sEmployeeNameId int
DECLARE @.sAgentid int
SELECT @.sCalldate ='20050712'
SELECT @.sEmployeeNameId = '0'
SELECT @.sAgentid ='0'
CREATE TABLE #tmPunchTime
(
Scalldate datetime,
sRawLogout sysname
)
Insert #tmPunchTime
Values ('20050712','x')
Insert #tmPunchTime
Values ('20050712','x')
Insert #tmPunchTime
Values ('20050712','x')
SELECT @.RC =(SELECT Count(*)
FROM #tmPunchTime
WHERE convert(nvarchar,scalldate,112) = @.sCallDate
AND ISNULL(sRawLogout, 'x') = 'x')
SELECT @.RC
DROP TABLE #tmPunchTime
"ninel" wrote:

> I have to get a count of records using dynamic sql.
> I have the following and I'm getting errors (Syntax error converting
> the varchar value 'SELECT ' to a column of data type int.):
> DECLARE @.sCalldate varchar(10)
> DECLARE @.SQL varchar(8000)
> DECLARE @.RC int
> SELECT @.sCalldate ='20050712'
> SELECT @.sEmployeeNameId = '0'
> SELECT @.sAgentid ='0'
> SELECT @.SQL = 'SELECT ' + @.RC + ' = Count(*)
> FROM dbo.tmPunchTime
> WHERE scalldate = ' + @.sCallDate +
> ' AND ISNULL(sRawLogout, ''x'') = ''x'''
> EXEC (@.SQL)
> How can I make this work?
> Thanks,
> Ninel
>

Wednesday, February 15, 2012

dynamic SQL error

Help,
I have the following procedure declared as a test for dynamic sql. The
resulting SQL statment is correct but I am getting syntax errors when the
dynamic SQL runs. taking the body of the procedure and running it from SQL
Analyzer window
creates the correct result. If I rewrite it to remove the parameters it
alos runs correctly. What am I missing to have this run corrcetly with
parameters
Declare @.Accountname varchar(50)
DECLARE @.@.OrgID int
DECLARE @.@.MonthID int
DECLARE @.@.W

int
DECLARE @.@.AccountID int
DECLARE @.SQL nvarchar(4000), @.ParmDefinition nvarchar(500)
Set @.@.Orgid=6
Set @.@.Monthid=1
Set @.@.W

=2
Set @.@.Accountid=3
Set @.Accountname = 'IP'
Begin
Set @.SQL = N'INSERT INTO [dynasight].[BudgetData]( [DatOrgID], [DatMonthID],
[DatW

], [DatAccountID], [DatValue] )
SELECT @.@.OrgId,@.@.MonthId,@.@.W

,@.@.AccountId, 99
FROM dynasight.BudgetAccounts where AccountID = @.@.AccountID'
SET @.ParmDefinition = N'@.@.OrgID int, @.@.MonthID int, @.@.W

int, @.@.AccountID
int'
exec sp_executesql @.SQL,@.ParmDefinition,@.@.Orgid= 6,@.@.MonthID = 1, @.@.W

= 2,
@.@.AccountID= 3
End
create PROCEDURE [dbo].[test]
AS
Declare @.SQL nvarchar(4000), @.ParmDefinition nvarchar
Begin
Set @.SQL = N'INSERT INTO [dynasight].[BudgetData]( [DatOrgID], [DatMonthID],
[DatW

], [DatAccountID], [DatValue] )
SELECT @.@.OrgId,@.@.MonthId,@.@.W

,@.@.AccountId, (SELECT
[DBo].[CalcIP](@.@.OrgId,@.@.MonthId,@.@.W

))
FROM dynasight.BudgetAccounts where AccountID = @.@.AccountID'
SET @.ParmDefinition = N'@.@.OrgID int, @.@.MonthID int, @.@.W

int, @.@.AccountID
int'
exec sp_executesql @.SQL,@.ParmDefinition,@.@.Orgid= 6,@.@.MonthID = 1, @.@.W

= 2,
@.@.AccountID= 3
print @.SQL
End
EXEC [HYP-MOR].[dynasight].[TEST]
yeild the following result
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Server: Msg 137, Level 15, State 1, Line 2
Must declare the variable '@.@.OrgId'.
INSERT INTO [dynasight].[BudgetData]( [DatOrgID], [DatMonthID], [DatW

],
[DatAccountID], [DatValue] )
SELECT @.@.OrgId,@.@.MonthId,@.@.W

,@.@.AccountId, 99
FROM dynasight.BudgetAccounts where AccountID = @.@.AccountIDHi,
are your sql server in collation CS ? Wich I think so ?
So there is a mismatch between the declare @.@.OrgID and the use in your
stored proc : @.@.OrgId
A +
ahuntertate a écrit :
> Help,
> I have the following procedure declared as a test for dynamic sql. The
> resulting SQL statment is correct but I am getting syntax errors when the
> dynamic SQL runs. taking the body of the procedure and running it from SQ
L
> Analyzer window
> creates the correct result. If I rewrite it to remove the parameters it
> alos runs correctly. What am I missing to have this run corrcetly with
> parameters
> Declare @.Accountname varchar(50)
> DECLARE @.@.OrgID int
> DECLARE @.@.MonthID int
> DECLARE @.@.W

int
> DECLARE @.@.AccountID int
> DECLARE @.SQL nvarchar(4000), @.ParmDefinition nvarchar(500)
> Set @.@.Orgid=6
> Set @.@.Monthid=1
> Set @.@.W

=2
> Set @.@.Accountid=3
> Set @.Accountname = 'IP'
>
> Begin
> Set @.SQL = N'INSERT INTO [dynasight].[BudgetData]( [DatOrgID], [DatMonthID],
> [DatW

], [DatAccountID], [DatValue] )
> SELECT @.@.OrgId,@.@.MonthId,@.@.W

,@.@.AccountId, 99
> FROM dynasight.BudgetAccounts where AccountID = @.@.AccountID'
> SET @.ParmDefinition = N'@.@.OrgID int, @.@.MonthID int, @.@.W

int, @.@.AccountI
D
> int'
>
> exec sp_executesql @.SQL,@.ParmDefinition,@.@.Orgid= 6,@.@.MonthID = 1, @.@.W

=
2,
> @.@.AccountID= 3
> End
>
>
> create PROCEDURE [dbo].[test]
> AS
> Declare @.SQL nvarchar(4000), @.ParmDefinition nvarchar
>
> Begin
> Set @.SQL = N'INSERT INTO [dynasight].[BudgetData]( [DatOrgID], [DatMonthID],
> [DatW

], [DatAccountID], [DatValue] )
> SELECT @.@.OrgId,@.@.MonthId,@.@.W

,@.@.AccountId, (SELECT
> [DBo].[CalcIP](@.@.OrgId,@.@.MonthId,@.@.W

))
> FROM dynasight.BudgetAccounts where AccountID = @.@.AccountID'
> SET @.ParmDefinition = N'@.@.OrgID int, @.@.MonthID int, @.@.W

int, @.@.AccountI
D
> int'
>
> exec sp_executesql @.SQL,@.ParmDefinition,@.@.Orgid= 6,@.@.MonthID = 1, @.@.W

=
2,
> @.@.AccountID= 3
> print @.SQL
> End
>
> EXEC [HYP-MOR].[dynasight].[TEST]
> yeild the following result
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> Server: Msg 137, Level 15, State 1, Line 2
> Must declare the variable '@.@.OrgId'.
> INSERT INTO [dynasight].[BudgetData]( [DatOrgID], [DatMonthID], [DatW

],
> [DatAccountID], [DatValue] )
> SELECT @.@.OrgId,@.@.MonthId,@.@.W

,@.@.AccountId, 99
> FROM dynasight.BudgetAccounts where AccountID = @.@.AccountID
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||I get a different error message (stating that the table doesn't exist, which
is correct). Make sure
that you use correct casing. Perhaps you are on a case sensitive SQL Server.
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ahuntertate" <ahuntertate@.discussions.microsoft.com> wrote in message
news:8B7E4A21-FA48-4BA5-8DC6-104666C3C1E5@.microsoft.com...
> Help,
> I have the following procedure declared as a test for dynamic sql. The
> resulting SQL statment is correct but I am getting syntax errors when the
> dynamic SQL runs. taking the body of the procedure and running it from SQ
L
> Analyzer window
> creates the correct result. If I rewrite it to remove the parameters it
> alos runs correctly. What am I missing to have this run corrcetly with
> parameters
> Declare @.Accountname varchar(50)
> DECLARE @.@.OrgID int
> DECLARE @.@.MonthID int
> DECLARE @.@.W