Formatting Generated SQL Statements

[Joe]

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

 

[Bob Barrows]

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.

 

[Joe]

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

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

 

[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

 

[Joe]

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

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]

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

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