Invalid column name in SP

I spend over 3 hrs to debug (The following store procedure, I always got
invalid column at The @.reportId) . I had used the same approach in other SP
.
and everything goes fine. BUT today, I don't know what's going on. Please
Help ~~~
EXEC sel_pl_acctjobperiod '200312','0001','HLSHK','DTS_ACCOUNT.DBO'
I got [invalid column name '0001',invalid column name '200312',invalid
column name 'HLSHK'.]
CREATE PROCEDURE dbo.sel_pl_acctjobperiod
@.jobperiod nvarchar(6),
@.reportid nvarchar(20),
@.BranchID nvarchar(10),
@.dbcname_dest varchar(20)
AS
SET NOCOUNT ON
DECLARE @.sql_insert varchar(4000)
DECLARE @.sql_sel_debit varchar(4000)
DECLARE @.sql_arinv_debit varchar(4000)
DECLARE @.sql_where varchar(4000)
DECLARE @.sql_arinv_debit_exec nvarchar(4000)
SELECT @.sql_insert = 'insert into tmp_pl_acctinfo
(deptid,branchid,reportid,smancode,jobno
,invno,iemtype,jobperiod ,
TtlIncome, TtlExpenses, TtlNetProfit,doctype) '
SELECT @.sql_sel_debit ='select Info.deptid,Info.branchid,"' + @.reportid + '"
as reportid ,'''' as
smancode,Info.JobNo,Info.invno,Info.iemtype,Info.jobperiod ,info.ttlbaseamt
as ttlincome, 0 as ttlexpenses, 0 as ttlnetprofit,'
SELECT @.sql_arinv_debit =' '''' as doctype from ' + @.dbcname_dest +
'.arinvinfo Info where Info.validsw = 1 and Info.accttype = "DEBIT" '
SELECT @.sql_where = ' and Info.jobperiod="' + @.jobperiod + '" and
Info.branchid="' + @.branchid + '"'
SELECT @.sql_arinv_debit_exec = @.sql_insert + @.sql_sel_debit +
@.sql_arinv_debit + @.sql_where
print @.sql_arinv_debit_exec
EXEC (@.sql_arinv_debit_exec)Try SET QUOTED_IDENTIFIER OFF then drop and re-create the proc.
Alternatively, change your double quotes (") into two single quotes
(''), otherwise they may be read as column delimiters.
The QUOTED_IDENTIFIER setting is saved with each proc so take notice of
that setting whenever you use CREATE PROC.
David Portas
SQL Server MVP
--|||Hi
You seem to have double quotes in your command string (@.sql_sel_debit),
therefore you may have QUOTED_IDENTIFIER ON. If you want to escape a single
quote use another single quote.
John
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:OuUg0lpQFHA.3144@.tk2msftngp13.phx.gbl...
>I spend over 3 hrs to debug (The following store procedure, I always got
>invalid column at The @.reportId) . I had used the same approach in other
>SP .
> and everything goes fine. BUT today, I don't know what's going on. Please
> Help ~~~
> EXEC sel_pl_acctjobperiod '200312','0001','HLSHK','DTS_ACCOUNT.DBO'
> I got [invalid column name '0001',invalid column name '200312',invalid
> column name 'HLSHK'.]
> CREATE PROCEDURE dbo.sel_pl_acctjobperiod
> @.jobperiod nvarchar(6),
> @.reportid nvarchar(20),
> @.BranchID nvarchar(10),
> @.dbcname_dest varchar(20)
>
> AS
> SET NOCOUNT ON
> DECLARE @.sql_insert varchar(4000)
> DECLARE @.sql_sel_debit varchar(4000)
> DECLARE @.sql_arinv_debit varchar(4000)
> DECLARE @.sql_where varchar(4000)
> DECLARE @.sql_arinv_debit_exec nvarchar(4000)
>
> SELECT @.sql_insert = 'insert into tmp_pl_acctinfo
> (deptid,branchid,reportid,smancode,jobno
,invno,iemtype,jobperiod ,
> TtlIncome, TtlExpenses, TtlNetProfit,doctype) '
> SELECT @.sql_sel_debit ='select Info.deptid,Info.branchid,"' + @.reportid +
> '" as reportid ,'''' as
> smancode,Info.JobNo,Info.invno,Info.iemtype,Info.jobperiod
> ,info.ttlbaseamt as ttlincome, 0 as ttlexpenses, 0 as ttlnetprofit,'
> SELECT @.sql_arinv_debit =' '''' as doctype from ' + @.dbcname_dest +
> '.arinvinfo Info where Info.validsw = 1 and Info.accttype = "DEBIT" '
> SELECT @.sql_where = ' and Info.jobperiod="' + @.jobperiod + '" and
> Info.branchid="' + @.branchid + '"'
> SELECT @.sql_arinv_debit_exec = @.sql_insert + @.sql_sel_debit +
> @.sql_arinv_debit + @.sql_where
>
> print @.sql_arinv_debit_exec
> EXEC (@.sql_arinv_debit_exec)
>|||Replace your double quotes with 2 single-quotes:
CREATE PROCEDURE dbo.sel_pl_acctjobperiod
@.jobperiod nvarchar(6),
@.reportid nvarchar(20),
@.BranchID nvarchar(10),
@.dbcname_dest varchar(20)
AS
SET NOCOUNT ON
DECLARE @.sql_insert varchar(4000)
DECLARE @.sql_sel_debit varchar(4000)
DECLARE @.sql_arinv_debit varchar(4000)
DECLARE @.sql_where varchar(4000)
DECLARE @.sql_arinv_debit_exec nvarchar(4000)
SELECT @.sql_insert = 'insert into tmp_pl_acctinfo
(deptid,branchid,reportid,smancode,jobno
,invno,iemtype,jobperiod ,
TtlIncome, TtlExpenses, TtlNetProfit,doctype) '
SELECT @.sql_sel_debit ='select Info.deptid,Info.branchid,''' + @.reportid +
'''
as reportid ,'''' as
smancode,Info.JobNo,Info.invno,Info.iemtype,Info.jobperiod ,info.ttlbaseamt
as ttlincome, 0 as ttlexpenses, 0 as ttlnetprofit,'
SELECT @.sql_arinv_debit =' '''' as doctype from ' + @.dbcname_dest +
'.arinvinfo Info where Info.validsw = 1 and Info.accttype = ''DEBIT'''
SELECT @.sql_where = ' and Info.jobperiod=''' + @.jobperiod + ''' and
Info.branchid=''' + @.branchid + ''''
SELECT @.sql_arinv_debit_exec = @.sql_insert + @.sql_sel_debit +
@.sql_arinv_debit + @.sql_where
print @.sql_arinv_debit_exec
go
That said, you should avoid using dynamic SQL. It's hard to debug, as you
can see.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:OuUg0lpQFHA.3144@.tk2msftngp13.phx.gbl...
I spend over 3 hrs to debug (The following store procedure, I always got
invalid column at The @.reportId) . I had used the same approach in other SP
.
and everything goes fine. BUT today, I don't know what's going on. Please
Help ~~~
EXEC sel_pl_acctjobperiod '200312','0001','HLSHK','DTS_ACCOUNT.DBO'
I got [invalid column name '0001',invalid column name '200312',invalid
column name 'HLSHK'.]
CREATE PROCEDURE dbo.sel_pl_acctjobperiod
@.jobperiod nvarchar(6),
@.reportid nvarchar(20),
@.BranchID nvarchar(10),
@.dbcname_dest varchar(20)
AS
SET NOCOUNT ON
DECLARE @.sql_insert varchar(4000)
DECLARE @.sql_sel_debit varchar(4000)
DECLARE @.sql_arinv_debit varchar(4000)
DECLARE @.sql_where varchar(4000)
DECLARE @.sql_arinv_debit_exec nvarchar(4000)
SELECT @.sql_insert = 'insert into tmp_pl_acctinfo
(deptid,branchid,reportid,smancode,jobno
,invno,iemtype,jobperiod ,
TtlIncome, TtlExpenses, TtlNetProfit,doctype) '
SELECT @.sql_sel_debit ='select Info.deptid,Info.branchid,"' + @.reportid + '"
as reportid ,'''' as
smancode,Info.JobNo,Info.invno,Info.iemtype,Info.jobperiod ,info.ttlbaseamt
as ttlincome, 0 as ttlexpenses, 0 as ttlnetprofit,'
SELECT @.sql_arinv_debit =' '''' as doctype from ' + @.dbcname_dest +
'.arinvinfo Info where Info.validsw = 1 and Info.accttype = "DEBIT" '
SELECT @.sql_where = ' and Info.jobperiod="' + @.jobperiod + '" and
Info.branchid="' + @.branchid + '"'
SELECT @.sql_arinv_debit_exec = @.sql_insert + @.sql_sel_debit +
@.sql_arinv_debit + @.sql_where
print @.sql_arinv_debit_exec
EXEC (@.sql_arinv_debit_exec)|||Is there some reason that you are using dynamic SQL and passing a table
name to a stored procedure? Is this an accounting system in which you
have no idea where the data is until run time? SQL injection and the
destruction of cohesion in the module would seem to tell us that this
is a bad programming style.
Are the job_period, report_id and branch_id really varying length
national characters? Most systems do not have encoding like that. Why
are you inviting bad data?
Next, the name of the table being loaded implies it is a temp table,
which would imply that you have a procedural design and not a
relational one. Are you actually building things step by step and
storing the intermediate results in working tables (aka "Cobol scratch
files in SQL disguise")?|||Thanks All.
I dont' know why my other SP didn't got such problem.
I never use SET QUOTED_IDENTIFIER OFF . before
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
'?:1113666233.068362.325440@.z14g2000cwz.googlegroups.com...
> Try SET QUOTED_IDENTIFIER OFF then drop and re-create the proc.
> Alternatively, change your double quotes (") into two single quotes
> (''), otherwise they may be read as column delimiters.
> The QUOTED_IDENTIFIER setting is saved with each proc so take notice of
> that setting whenever you use CREATE PROC.
> --
> David Portas
> SQL Server MVP
> --
>