Error in Dynamic SQL.....help!!

Can someone please help me in troubleshooting the code below. I have a table called credit_app_table_status which is based on the following create statement:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[credit_app_table_status]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[credit_app_table_status]
GO

CREATE TABLE [dbo].[credit_app_table_status] (
[table_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_period_start] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_period_end] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

When I run the code below, it gives me the following error message. I have tried to change the data types of bill_period_start and bill_period_end apart from changing the code, but it doesn't work. Instead of @.bill_period_start and @.bill_period_end, if I have actual dates, it works. Can someone pleaseeeee help.

/* Error Message
Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near '1'.
Server: Msg 156, Level 15, State 1, Line 27
Incorrect syntax near the keyword 'and'.
*/

--Code

Declare
@.SQL VarChar(2000),
@.tablename varchar (20),
@.bill_period_start varchar (20),
@.bill_period_end varchar (20)

SELECT @.tablename = table_name from credit_app_table_status
SELECT @.bill_period_start = bill_period_start from credit_app_table_status
SELECT @.bill_period_end = bill_period_end from credit_app_table_status

SELECT @.SQL = 'insert into ' + @.TableName + ' (
bill_period_begin,
bill_period_end,
org_id,
bill_type_cn,
qty,
rate,
total_amt,
add_user_id,
add_date
)
select '
+ @.bill_period_start +
','
+ @.bill_period_end +
',
a.org_id,
14,
sum(a.orders)as qty,
-.75 as rate,
sum(a.orders) * -.75,
1493,
getdate()
from cph..tblPkgFlatDaily a
where a.priority_cn = 0
and a.org_id in
(select org_id
from csorg_ins_group where
group_type_cn not in (7,8,9,10)
)
and a.ship_date between '
+ @.bill_period_start +
' and '
+ @.bill_period_end +
'group by a.org_id order by a.org_id'

Exec ( @.SQL)

GO+ @.bill_period_end +
'group by a.org_id order by a.org_id'


Exec ( @.SQL)

GO
It looks like your group by does not have a space preceeding it.

In your testing, why don't you select @.sql and view it first, and then when it looks good try the execution?|||It looks like your group by does not have a space preceeding it.

In your testing, why don't you select @.sql and view it first, and then when it looks good try the execution?
Thanks for the information Tomh3. The problem that I saw was that
@.bill_period_start and @.bill_period_end didn't have the single quotes such as in:
'Sep 1 2004 12:00AM'. Can you please let me know how I can get single quotes in @.bill_period_start and @.bill_period_end.

Thank you so much!!

Below is the code I received using select @.sql:

insert into csorg_billing_flash
(bill_period_begin,
bill_period_end,
org_id,
bill_type_cn,
qty,
rate,
total_amt,
add_user_id,
add_date)
select
Sep 1 2004 12:00AM,
Oct 31 2004 12:00AM ,
a.org_id,
14,
sum(a.orders)as qty,
-.75 as rate,
sum(a.orders) * -.75,
1493,
getdate()
from cph..tblPkgFlatDaily a
where a.priority_cn = 0
and a.org_id in
(select org_id
from csorg_ins_group
where group_type_cn not in (7,8,9,10)
)
and a.ship_date between
Sep 1 2004 12:00AM and Oct 31 2004 12:00AM
group by a.org_id
order by a.org_id|||DECLARE @.bill_period_start datetime
SELECT @.bill_period_start = 'Sep 1 2004 12:00AM'
SELECT ''''+CONVERT(varchar(25),@.bill_period_start)+''''|||Thanks for the reply, Brett.

I think I am very close to getting query in shape. However, when I use
SELECT @.bill_period_start = ''''+CONVERT(varchar(25),@.bill_period_start)+''''

I receive the following error:
Server: Msg 241, Level 16, State 1, Line 24
Syntax error converting datetime from character string.

I will really appreciate if you could please help in troubleshooting this piece of code:

Declare
@.SQL VarChar(2000),
@.tablename varchar (20),
@.bill_period_start datetime,
@.bill_period_end datetime

SELECT @.tablename = table_name from credit_app_table_status
SELECT @.bill_period_start = ''''+CONVERT(varchar(25),@.bill_period_start)+'''' from credit_app_table_status
SELECT @.bill_period_end = ''''+CONVERT(varchar(25),@.bill_period_end)+'''' from credit_app_table_status

SELECT @.SQL = 'insert into ' + @.TableName + ' (
bill_period_begin,
bill_period_end,
org_id,
bill_type_cn,
qty,
rate,
total_amt,
add_user_id,
add_date
)
select '
+ @.bill_period_start +
', '
+ @.bill_period_end +
',
a.org_id,
14,
sum(a.orders)as qty,
-.75 as rate,
sum(a.orders) * -.75,
1493,
getdate()
from cph..tblPkgFlatDaily a
where a.priority_cn = 0
and a.org_id in
(select org_id
from csorg_ins_group where
group_type_cn not in (7,8,9,10)
)
and a.ship_date between '
+ @.bill_period_start +
'and '
+ @.bill_period_end +
'group by a.org_id order by a.org_id'

SELECT (@.SQL)

GO|||Thanks for the reply, Brett.

I think I am very close to getting query in shape. However, when I use
SELECT @.bill_period_start = ''''+CONVERT(varchar(25),@.bill_period_start)+''''

I receive the following error:
Server: Msg 241, Level 16, State 1, Line 24
Syntax error converting datetime from character string.

I will really appreciate if you could please help in troubleshooting this piece of code:

SELECT @.tablename = table_name from credit_app_table_status
SELECT @.bill_period_start = ''''+CONVERT(varchar(25),@.bill_period_start)+'''' from credit_app_table_status
SELECT @.bill_period_end = ''''+CONVERT(varchar(25),@.bill_period_end)+'''' from credit_app_table_status


Oh so close. Try this

SELECT @.bill_period_start = ''''+CONVERT(varchar(25),@.bill_period_start,120)+' ''' from credit_app_table_status
SELECT @.bill_period_end = ''''+CONVERT(varchar(25),@.bill_period_end,120)+''' ' from credit_app_table_status

That will force the string into yyyy-mm-dd format instead of the default presentation format you showed us in the previous output.

And don't forget to keep Brett's additional apostrophes.

FYI, when I do something like this I create a variable named @.apos and populate it with a single apostrophe. So my string concatenation would read like ... + @.apos + @.string_variable + @.apos + ... it makes it easier for me to separate double apostrophe ('') from quote(").|||Oh so close. Try this

SELECT @.bill_period_start = ''''+CONVERT(varchar(25),@.bill_period_start,120)+' ''' from credit_app_table_status
SELECT @.bill_period_end = ''''+CONVERT(varchar(25),@.bill_period_end,120)+''' ' from credit_app_table_status

That will force the string into yyyy-mm-dd format instead of the default presentation format you showed us in the previous output.

And don't forget to keep Brett's additional apostrophes.

FYI, when I do something like this I create a variable named @.apos and populate it with a single apostrophe. So my string concatenation would read like ... + @.apos + @.string_variable + @.apos + ... it makes it easier for me to separate double apostrophe ('') from quote(").
Sorry, but I am still getting the same error message. Also I tried to run it having an apostrophe around @.bill_period_start and @.bill_period_end and still it doesn't work.
For example:

+ ' @.bill_period_start ' +
'and '
+ ' @.bill_period_end ' +|||SELECT @.bill_period_start = ''''+CONVERT(varchar(25),@.bill_period_start)+''''

I receive the following error:
Server: Msg 241, Level 16, State 1, Line 24
Syntax error converting datetime from character string.

That doesn't make sense...

Can you post the DDL of the Table...

And do this as well...

SELECT * FROM cph..tblPkgFlatDaily
WHERE ISNULL(ship_date)=0

If you get anything back from that, you have data problems...|||--Here is the DDL for credit_app_table_status

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[credit_app_table_status]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[credit_app_table_status]
GO

CREATE TABLE [dbo].[credit_app_table_status] (
[table_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_period_start] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_period_end] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

--Here is the DDL for tblPkgFlatDaily

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPkgFlatDaily]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPkgFlatDaily]
GO

CREATE TABLE [dbo].[tblPkgFlatDaily] (
[org_id] [int] NULL ,
[Orders] [int] NULL ,
[priority_cn] [int] NOT NULL ,
[ship_date] [datetime] NULL
) ON [PRIMARY]
GO|||SELECT @.bill_period_start = ''''+CONVERT(varchar(25),@.bill_period_start)+''''

I receive the following error:
Server: Msg 241, Level 16, State 1, Line 24
Syntax error converting datetime from character string.

That doesn't make sense...

Can you post the DDL of the Table...

And do this as well...

SELECT * FROM cph..tblPkgFlatDaily
WHERE ISNULL(ship_date)=0

If you get anything back from that, you have data problems...
--Here is the DDL for credit_app_table_status

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[credit_app_table_status]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[credit_app_table_status]
GO

CREATE TABLE [dbo].[credit_app_table_status] (
[table_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_period_start] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_period_end] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

--Here is the DDL for tblPkgFlatDaily

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPkgFlatDaily]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPkgFlatDaily]
GO

CREATE TABLE [dbo].[tblPkgFlatDaily] (
[org_id] [int] NULL ,
[Orders] [int] NULL ,
[priority_cn] [int] NOT NULL ,
[ship_date] [datetime] NULL
) ON [PRIMARY]
GO