Formatting Generated SQL Statements

Discussion in 'ASP General' started by Joe, Dec 9, 2008.

  1. Joe

    Joe Guest

    I am building a small helper application to create a table, stored
    procedures and triggers. I need to output the SQL formatted instead
    of all together. For instance, here is an Update Trigger that I have
    generated,

    CREATE TRIGGER updTrips ON dbo.Trips AFTER UPDATE AS IF @@ROWCOUNT = 0
    RETURN INSERT INTO [dbo].[Audit_Changes] ([PrimaryID],
    [ColumnChanged], [TableChanged], [OldValue], [NewValue], [Username],
    [DTChanged], [ActionType]) SELECT i.TripID, CASE col# WHEN 2 THEN
    'TripDate' WHEN 3 THEN 'StartTime' WHEN 4 THEN 'EndTime' ELSE '?' END,
    'Trips', CASE col# WHEN 2 THEN CAST(d.TripDate AS VARCHAR(25)) WHEN 3
    THEN d.StartTime WHEN 4 THEN d.EndTime ELSE '?' END, CASE col# WHEN 2
    THEN CAST(i.TripDate AS VARCHAR(25)) WHEN 3 THEN i.StartTime WHEN 4
    THEN i.EndTime ELSE '?' END, SUSER_SNAME(), GETDATE(), 'U' FROM
    inserted i INNER JOIN deleted d ON i.TripID = d.TripID CROSS JOIN
    ( SELECT 2 AS col# UNION ALL SELECT 3 AS col# UNION ALL SELECT 4 AS
    col# ) AS col#s WHERE ISNULL(CASE col# WHEN 2 THEN CAST(i.TripDate AS
    VARCHAR(25)) WHEN 3 THEN i.StartTime WHEN 4 THEN i.EndTime ELSE '?'
    END, '') <> ISNULL(CASE col# WHEN 2 THEN CAST(d.TripDate AS VARCHAR
    (25)) WHEN 3 THEN d.StartTime WHEN 4 THEN d.EndTime ELSE '?' END, '')

    Which is very, very messy... I would like to figure out how to format
    the SQL so it looks something like this,

    CREATE TRIGGER updTrips
    ON dbo.Trips
    AFTER UPDATE
    AS
    IF @@ROWCOUNT = 0
    RETURN
    INSERT INTO [dbo].[Audit_Changes] ([PrimaryID], [ColumnChanged],
    [TableChanged], [OldValue], [NewValue], [Username], [DTChanged],
    [ActionType])
    SELECT i.TripID,
    CASE col#
    WHEN 2 THEN 'TripDate'
    WHEN 3 THEN 'StartTime'
    WHEN 4 THEN 'EndTime'
    WHEN 5 THEN 'Duration'
    WHEN 6 THEN 'RLU'
    WHEN 7 THEN 'TripPlace'
    WHEN 8 THEN 'TripPurpose'
    ELSE '?' END,
    'Trips',
    CASE col#
    WHEN 2 THEN CAST(d.TripDate AS VARCHAR(25))
    WHEN 3 THEN d.StartTime
    WHEN 4 THEN d.EndTime
    WHEN 5 THEN d.Duration
    WHEN 6 THEN d.RLU
    WHEN 7 THEN d.TripPlace
    WHEN 8 THEN d.TripPurpose
    ELSE '?' END,
    CASE col#
    WHEN 2 THEN CAST(i.TripDate AS VARCHAR(25))
    WHEN 3 THEN i.StartTime
    WHEN 4 THEN i.EndTime
    WHEN 5 THEN i.Duration
    WHEN 6 THEN i.RLU
    WHEN 7 THEN i.TripPlace
    WHEN 8 THEN i.TripPurpose
    ELSE '?' END,
    SUSER_SNAME(),
    GETDATE(),
    'U'
    FROM inserted i
    INNER JOIN deleted d ON i.TripID = d.TripID
    CROSS JOIN (
    SELECT 2 AS col# UNION ALL
    SELECT 3 AS col# UNION ALL
    SELECT 4 AS col# UNION ALL
    SELECT 5 AS col# UNION ALL
    SELECT 6 AS col# UNION ALL
    SELECT 7 AS col# UNION ALL
    SELECT 8 AS col# )
    AS col#s
    WHERE ISNULL(CASE col#
    WHEN 2 THEN CAST(i.TripDate AS VARCHAR(25))
    WHEN 3 THEN i.StartTime
    WHEN 4 THEN i.EndTime
    WHEN 5 THEN i.Duration
    WHEN 6 THEN i.RLU
    WHEN 7 THEN i.TripPlace
    WHEN 8 THEN i.TripPurpose
    ELSE '?' END, '') <>
    ISNULL(
    CASE col#
    WHEN 2 THEN CAST(d.TripDate AS VARCHAR(25))
    WHEN 3 THEN d.StartTime
    WHEN 4 THEN d.EndTime
    WHEN 5 THEN d.Duration
    WHEN 6 THEN d.RLU
    WHEN 7 THEN d.TripPlace
    WHEN 8 THEN d.TripPurpose
    ELSE '?' END, '')

    I have tried vbCrLf, vbNewLine, etc... and nothing seems to work. Any
    ideas?

    Thanks,
    Drew
     
    Joe, Dec 9, 2008
    #1
    1. Advertising

  2. Joe

    Bob Barrows Guest

    Joe wrote:
    > I am building a small helper application to create a table, stored
    > procedures and triggers. I need to output the SQL formatted instead
    > of all together.


    "Output" it where? In your HTML? If so, you either need to use the <PRE>
    tag, or use <br> for your line breaks.
    --
    HTH,
    Bob Barrows
     
    Bob Barrows, Dec 9, 2008
    #2
    1. Advertising

  3. Joe

    Joe Guest

    On Dec 9, 2:00 pm, "Bob Barrows" <> wrote:
    > Joe wrote:
    > > I am building a small helper application to create a table, stored
    > > procedures and triggers.  I need to output the SQL formatted instead
    > > of all together.

    >
    > "Output" it where? In your HTML? If so, you either need to use the <PRE>
    > tag, or use <br> for your line breaks.
    > --
    > HTH,
    > Bob Barrows


    No, the problem seems to be that when I use,

    select name as 'Trigger', object_name(parent_obj) as 'Table'
    from sysobjects
    where xtype = 'TR'

    to view the trigger in the database, the trigger is really unreadable,
    which is hard to troubleshoot. Is there anyway to do this?

    Thanks,
    Drew
     
    Joe, Dec 9, 2008
    #3
  4. Joe

    Bob Barrows Guest

    Joe wrote:
    > On Dec 9, 2:00 pm, "Bob Barrows" <> wrote:
    >> Joe wrote:
    >>> I am building a small helper application to create a table, stored
    >>> procedures and triggers. I need to output the SQL formatted instead
    >>> of all together.

    >>
    >> "Output" it where? In your HTML? If so, you either need to use the
    >> <PRE> tag, or use <br> for your line breaks.
    >> --
    >> HTH,
    >> Bob Barrows

    >
    > No, the problem seems to be that when I use,
    >
    > select name as 'Trigger', object_name(parent_obj) as 'Table'
    > from sysobjects
    > where xtype = 'TR'
    >
    > to view the trigger in the database, the trigger is really unreadable,
    > which is hard to troubleshoot. Is there anyway to do this?
    >

    View it where? in SSMS? or, if it's pre-2005 SS, Query Analyzer?

    How did the text get into the sysobjects table? Was it input into your
    html page? if so, what kind of element was used? Whatever you are doing
    to receive the input from the user and pass it to the database is
    causing the whitespace to be stripped
    --
    HTH,
    Bob Barrows
     
    Bob Barrows, Dec 9, 2008
    #4
  5. Joe

    Joe Guest

    On Dec 9, 2:56 pm, "Bob Barrows" <> wrote:
    > Joe wrote:
    > > On Dec 9, 2:00 pm, "Bob Barrows" <> wrote:
    > >> Joe wrote:
    > >>> I am building a small helper application to create a table, stored
    > >>> procedures and triggers. I need to output the SQL formatted instead
    > >>> of all together.

    >
    > >> "Output" it where? In your HTML? If so, you either need to use the
    > >> <PRE> tag, or use <br> for your line breaks.
    > >> --
    > >> HTH,
    > >> Bob Barrows

    >
    > > No, the problem seems to be that when I use,

    >
    > > select name as 'Trigger', object_name(parent_obj) as 'Table'
    > > from sysobjects
    > > where xtype = 'TR'

    >
    > > to view the trigger in the database, the trigger is really unreadable,
    > > which is hard to troubleshoot.  Is there anyway to do this?

    >
    > View it where? in SSMS? or, if it's pre-2005 SS, Query Analyzer?
    >
    > How did the text get into the sysobjects table? Was it input into your
    > html page? if so, what kind of element was used? Whatever you are doing
    > to receive the input from the user and pass it to the database is
    > causing the whitespace to be stripped
    > --
    > HTH,
    > Bob Barrows


    In Query Analyzer. I am using SQL Server 2000 (should've mentioned
    that earlier).

    I am dynamically creating the triggers (so I don't have to manually
    write them) using ASP... the ASP page is connecting to the DB and then
    executing the CREATE TRIGGER statement (as well as creating a table
    and a couple stored procedures) that has been generated by ASP. I
    just figured that there would be some way to format these statements
    before they were created on the SQL Server.

    Thanks,
    Drew
     
    Joe, Dec 9, 2008
    #5
  6. Joe

    Joe Guest

    On Dec 9, 2:56 pm, "Bob Barrows" <> wrote:
    > Joe wrote:
    > > On Dec 9, 2:00 pm, "Bob Barrows" <> wrote:
    > >> Joe wrote:
    > >>> I am building a small helper application to create a table, stored
    > >>> procedures and triggers. I need to output the SQL formatted instead
    > >>> of all together.

    >
    > >> "Output" it where? In your HTML? If so, you either need to use the
    > >> <PRE> tag, or use <br> for your line breaks.
    > >> --
    > >> HTH,
    > >> Bob Barrows

    >
    > > No, the problem seems to be that when I use,

    >
    > > select name as 'Trigger', object_name(parent_obj) as 'Table'
    > > from sysobjects
    > > where xtype = 'TR'

    >
    > > to view the trigger in the database, the trigger is really unreadable,
    > > which is hard to troubleshoot.  Is there anyway to do this?

    >
    > View it where? in SSMS? or, if it's pre-2005 SS, Query Analyzer?
    >
    > How did the text get into the sysobjects table? Was it input into your
    > html page? if so, what kind of element was used? Whatever you are doing
    > to receive the input from the user and pass it to the database is
    > causing the whitespace to be stripped
    > --
    > HTH,
    > Bob Barrows


    Just a note...

    The triggers and sps work fine... there is no problem there at all,
    but viewing and working with them is a pain considering they are just
    lumped together.

    Thanks,
    Drew
     
    Joe, Dec 9, 2008
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Neil Zanella
    Replies:
    8
    Views:
    1,196
    mfmehdi
    Oct 20, 2006
  2. Harry George
    Replies:
    6
    Views:
    385
    Bart Nessux
    Feb 23, 2004
  3. Vince
    Replies:
    12
    Views:
    751
    Martin Gregorie
    Jan 21, 2008
  4. JT
    Replies:
    1
    Views:
    120
    Bob Barrows [MVP]
    Sep 27, 2005
  5. John Crichton
    Replies:
    6
    Views:
    267
    John Crichton
    Jul 12, 2010
Loading...

Share This Page