Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2003
    Posts
    60

    Unanswered: 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

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by sajmera
    + @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?

  3. #3
    Join Date
    Aug 2003
    Posts
    60
    Quote Originally Posted by tomh53
    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

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    DECLARE @bill_period_start datetime
    SELECT @bill_period_start = 'Sep 1 2004 12:00AM'
    SELECT ''''+CONVERT(varchar(25),@bill_period_start)+''''
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Aug 2003
    Posts
    60
    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

  6. #6
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by sajmera
    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(").

  7. #7
    Join Date
    Aug 2003
    Posts
    60
    Quote Originally Posted by tomh53
    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 ' +

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    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...

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

    If you get anything back from that, you have data problems...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Aug 2003
    Posts
    60
    --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

  10. #10
    Join Date
    Aug 2003
    Posts
    60
    Quote Originally Posted by Brett Kaiser
    Code:
    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...

    Code:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •