T-SQL Formatting '@'

Note: Due to the nature of our business I have changed the names of database objects but the length is the same.

We are formating T-SQL scripts and having an issue with parameters being chopped off at the '@' with a line length of 40 or 80 or 100. To work around this we have set line length to 0 but this makes long lines hard to read when they go off the screen. Other SQL formatters handle this so it is not a T-SQL issue. Is there another way to correct this issue in SQL Assistant? Below is a sample script we use (with object names changed). Changing the length of object names will not work as many years of development have already completed and mgnt is not going to prioritize object name changes.

We are on SQL server 2005 with all patches and Service packs applied and using MS SQL Server Management Studio with all patches applied.

Thanks,
Philip

SET quoted_identifier OFF
GO
SET ansi_nulls OFF
GO

IF EXISTS (
SELECT *
FROM dbo.sysobjects WITH (nolock)
WHERE id = Object_id(N'[dbo].[ObjectnameHere0001]')
AND Objectproperty(id,N'IsProcedure') = 1
)

DROP PROCEDURE [dbo].[ObjectnameHere0001]
GO

CREATE PROCEDURE [dbo].[ObjectnameHere0001]
(
@Parameter01 INT,
@ParameterLong02 DATETIME,
@ParameterLong03 DATETIME,
@ParameterLong4 INT
,
@ParameterLong5 INT
-- (Filter Page this is LIST) 0=all,1=TableName40,2=not TableName40
,
@Parameter6 INT,
@Parameter7 INT,
@Param8 INT,
@ParameterVeryLong09 INT,
@Parameter10 VARCHAR(200),
@ParameterLong11 INT,
@Parameter12_1 INT,
@Parameter12_2 INT,
@Parameter12_3 INT,
@Parameter12_4 INT,
@Parameter12_5 INT,
@Param13 INT,
@Param14_1 INT,
@Param14_2 INT,
@Param14_3 INT,
@Param14_4 INT,
@Param14_5 INT,
@ParameterVeryLong15 INT,
@ParameterLong16_1 INT,
@ParameterLong16_2 INT,
@ParameterLong16_3 INT,
@ParameterLong16_4 INT,
@ParameterLong16_5 INT,
@ParameterVeryLong17 INT,
@Parameter18 INT
,
@Parameter19 INT
)
AS
SET nocount ON
DECLARE @mytable TABLE(
name VARCHAR(80)
,Col3 VARCHAR(20)
,account# VARCHAR(50)
,Column5 VARCHAR(50)
,ColumnLongName6 VARCHAR(200)
,ColumnName53 VARCHAR(100)
,Column54 VARCHAR(100)
,ColumnName55 VARCHAR(15)
,Column56 VARCHAR(200)
,ColumnLongName0051 DATETIME
,ThisMakesForColumnLongName6 VARCHAR(200)
,ColumnVeryLongNameHere13 VARCHAR(50)
,ColumnLongName17 DATETIME
,ColumnName18 DATETIME
,column00050 INT
,TableName40 VARCHAR(50)
,ColumnLongName19 VARCHAR(50)
,ColumnName20 TINYINT
,ColumnVeryLongName0021 TINYINT
,ColumnName22 TINYINT
)
DECLARE @ThisName60 INT
DECLARE @ThisName00061 BIT
DECLARE @CThisName0064 FLOAT
DECLARE @CThisName0065 VARCHAR(5)

SELECT @ThisName60 = t01.ThisName60,
@ThisName00061 = t01.ThisVeryLongNameHere62,
@CThisName0064 = mtz.ThisName67,
@CThisName0065 = mtz.CThisName0065eviation
FROM dbo.OhLongNameTableHere1 t01 WITH ( nolock )
JOIN dbo.TableNameHere60 mtz
ON t01.ThisName60 = mtz.ThisName60
WHERE t01.Column16 = @Parameter01

IF @@ROWCOUNT = 0
RETURN

EXEC OhLongNameProcedureHere1
@ThisName60,
@ThisName00061,
@CThisName0064,
@ParameterLong02 OUTPUT
EXEC OhLongNameProcedureHere1
@ThisName60,
@ThisName00061,
@CThisName0064,
@ParameterLong03 OUTPUT

SET
@Parameter10 = UPPER(@Parameter10) + '%' -- Allow partial entry

IF @ParameterLong4 = 0
BEGIN
PRINT 'OhVeryLongNameTableHere02...'
INSERT @mytable
(
name, Col3, account#, Column5, ColumnLongName6, ColumnName53, Column54, ColumnName55, Column56, ColumnLongName0051, ThisMakesForColumnLongName6, ColumnNameVeryLongHere15, ColumnLongName17, ColumnName18, column00050, TableName40, ColumnLongName19, ColumnName20, ColumnVeryLongName0021, ColumnName22
)
SELECT DISTINCT
CASE
WHEN (pa.Column01 = NULL
OR pa.Column01 = '')
AND (pa.Column02 = NULL
OR pa.Column02 = '') THEN ''
ELSE CASE
WHEN (pa.Column01 = NULL
OR pa.Column01 = '') THEN pa.Column02
ELSE CASE
WHEN (pa.Column02 = NULL
OR pa.Column02 = '') THEN pa.Column01
ELSE pa.Column01 +
', ' +
pa.Column02
END
END
END AS 'Column2',
pa.Col3 'Col3',
pa.ColuumnLongName4 'Column4#',
pa.Column5 'Column5',
pa.ColumnLongName6 'Column06 Label',
tb03.Col7 'Colummn7 Label',
t05.Col8 'Col8',
CASE
WHEN (ColumnLongName9 IS NOT NULL) THEN CONVERT(VARCHAR(15),
tb04.Col10)
ELSE 'Not Specified'
END 'Column10',
CASE
WHEN (t05.Col8 = NULL
AND t06.Col11 = NULL) THEN ''
ELSE CASE
WHEN (t05.Col8 = NULL) THEN t06.Col11
ELSE CASE
WHEN (t06.Col11 = NULL) THEN t05.Col8
ELSE t05.Col8 +
' - ' +
t06.Col11
END
END
END AS 'Column11',
CASE
WHEN (tb07.ColumnVeryLongNameHere12 IS NOT NULL)
-- 'Comment here'
THEN tb07.ColumnVeryLongNameHere12
ELSE CASE
WHEN (tb07.ColumnVeryLongNameHere13IS NOT NULL) THEN (SELECT tb022.ColumnLongName14
FROM dbo.VeryLongTableNamehere0000002
tb022 WITH (nolock)
WHERE tb022.ColuumnLongName4 = tb07.ColumnNameVeryLongHere15
AND tb022.Column16 = tb07.Column16)
ELSE NULL
END
END AS 'Column Label',
tb07.ThisMakesForColumnLongName6,
tb07.ColumnNameVeryLongHere15,
tb07.ColumnLongName17,
pa.ColumnName18 'Column Label',
CASE
WHEN (tb07.ColumnVeryLongNameHere12 IS NOT NULL) THEN Datediff(HOUR,tb07.ColumnVeryLongNameHere12,pa.ColumnName18)
/
24
ELSE ''
END 'column00050',
CASE
WHEN (tb07.ColumnLongName19 IS NOT NULL) THEN (SELECT tb07.ColumnLongName19
)
ELSE 'Not TableName40'
END 'Column Label',
tb07.ColumnLongName19,
tb07.ColumnName20,
tb07.ColumnVeryLongName0021,
tb07.ColumnName22
FROM dbo.OhVeryLongNameTableHere02 pa WITH (nolock)

LEFT OUTER JOIN dbo.OhLongTableName03 tb03 WITH (nolock)
ON tb03.ColumnName23 = pa.ColumnName23
AND tb03.Column16 = pa.Column16
LEFT OUTER JOIN dbo.TableName00000004 tb04 WITH (nolock)
ON ColumnLongName9 = tb04.ColumnName24
AND pa.Column16 = tb04.Column16
LEFT OUTER JOIN dbo.TableName05 t05 WITH (nolock)
ON t05.Column25 = pa.Column25
AND t05.Column16 = pa.Column16
LEFT OUTER JOIN dbo.TableName06 t06 WITH (nolock)
ON t06.Col25 = pa.Col25
AND t06.Column16 = pa.Column16
LEFT OUTER JOIN dbo.TableVeryLongName00000000000000007 tb07 WITH (nolock)
ON tb07.ColumnLongName26 = pa.ColumnLongName26
AND tb07.Column16 = pa.Column16
WHERE pa.Column16 = @Parameter01
AND pa.ColumnName27 = 1
AND pa.ColumnNameHere35 = 0
-- Comment here
AND pa.ColumnLongName14 IS NULL
AND (
(
@Parameter10 = NULL
OR @Parameter10 = ''
)
OR (
UPPER(pa.ColumnLongName6) LIKE @Parameter10
)
)
AND (
(@Param13= 0 )
OR (
(@Param13= 1 )
AND (
(
@Param14_1 IS NULL
AND @Param14_2 IS NULL
AND @Param14_3 IS NULL
AND @Param14_4 IS NULL
AND @Param14_5 IS NULL
)
OR (
pa.Column25 IN (@Param14_1, @Param14_2, @Param14_3, @Param14_4, @Param14_5)
)
)
)
OR (
(@Param13= 2 )
AND (
(
@Param14_1 IS NULL
AND @Param14_2 IS NULL
AND @Param14_3 IS NULL
AND @Param14_4 IS NULL
AND @Param14_5 IS NULL
)
OR (pa.Column25 IS NULL )
OR (
pa.Column25 NOT IN (Isnull(@Param14_1,0), Isnull(@Param14_2
,0), Isnull(@Param14_3,0), Isnull(@Param14_4,0), Isnull(@Param14_5,0))
)
)
)
)
AND (
(@ParameterLong11 = 0 )
OR (
(@ParameterLong11 = 1 )
AND (
(
@Parameter12_1 IS NULL
AND @Parameter12_2 IS NULL
AND @Parameter12_3 IS NULL
AND @Parameter12_4 IS NULL
AND @Parameter12_5 IS NULL
)
OR (
pa.ColumnName23 IN (@Parameter12_1, @Parameter12_2, @Parameter12_3, @Parameter12_4, @Parameter12_5)
)
)
)
OR (
(@ParameterLong11 = 2 )
AND (
(
@Parameter12_1 IS NULL
AND @Parameter12_2 IS NULL
AND @Parameter12_3 IS NULL
AND @Parameter12_4 IS NULL
AND @Parameter12_5 IS NULL
)
OR (pa.ColumnName23 IS NULL )
OR (
pa.ColumnName23 NOT IN (Isnull(@Parameter12_1,0), Isnull(@Parameter12_2,0), Isnull(@Parameter12_3,0), Isnull(@Parameter12_4,0), Isnull(@Parameter12_5,0))
)
)
)
)
AND (
(@ParameterVeryLong15 = 0 )
OR (
(@ParameterVeryLong15 = 1 )
AND (
(
@ParameterLong16_1 IS NULL
AND @ParameterLong16_2 IS NULL
AND @ParameterLong16_3 IS NULL
AND @ParameterLong16_4 IS NULL
AND @ParameterLong16_5 IS NULL
)
OR (
pa.ColumnName28 IN (@ParameterLong16_1, @ParameterLong16_2, @ParameterLong16_3, @ParameterLong16_4, @ParameterLong16_5)
)
)
)
OR (
(@ParameterVeryLong15 = 2 )
AND (
(
@ParameterLong16_1 IS NULL
AND @ParameterLong16_2 IS NULL
AND @ParameterLong16_3 IS NULL
AND @ParameterLong16_4 IS NULL
AND @ParameterLong16_5 IS NULL
)
OR (pa.ColumnName28 IS NULL )
OR (
pa.ColumnName28 NOT IN (Isnull(@ParameterLong16_1
,0), Isnull(@ParameterLong16_2,0), Isnull(@ParameterLong16_3,0), Isnull(@ParameterLong16_4,0), Isnull(@ParameterLong16_5,0))
)
)
)
)
AND (
(Isnull(@Parameter6,0) = 0 )
OR (
pa.Column5 IN (SELECT name
FROM dbo.ColumnName29 WITH (nolock)
WHERE Column5id = @Parameter6)
)
)
AND (
(Isnull(@ParameterVeryLong09,0) = 0 )
OR (
tb07.ColumnLongName19 IN (SELECT name
FROM [dbo].[Some001ColumnLongName19s]
WITH (nolock)
WHERE ColumnLongName19id = @ParameterVeryLong09)
)
)
AND (
(Isnull(@ParameterVeryLong17,0) = 0 )
OR (
pa.attendingphysicianid = @ParameterVeryLong17
)
)
AND (
Isnull(@Parameter18,0) = 0
OR (
EXISTS (
SELECT 1
FROM dbo.VeryLongTableNamehere0000002 tb022
WITH (nolock)
JOIN dbo.TableNameHere07 TabAlias WITH (nolock)
ON (
TabAlias.Col30 = tb022.finalCol30
AND TabAlias.code = @Parameter18
)
WHERE tb022.ColuumnLongName4 = tb07.ColumnNameVeryLongHere15
AND tb022.Column16 = tb07.Column16
)
)
)
AND (
Isnull(@Parameter19,0) = 0
OR (
EXISTS (
SELECT 1
FROM dbo.VeryLongTableNamehere0000002 tb022
WITH (nolock)
JOIN dbo.TableNameHere07 TabAlias WITH (nolock)
ON (
TabAlias.Col30 = tb022.finalCol30
AND TabAlias.drgyear = @Parameter19
)
WHERE tb022.ColuumnLongName4 = tb07.ColumnNameVeryLongHere15
AND tb022.Column16 = tb07.Column16
)
)
)
END
ELSE -- Comment Here
BEGIN
PRINT 'VeryLongTableNamehere0000002...'
INSERT @mytable
(
name, Col3, account#, Column5, ColumnLongName6, ColumnName53, Column54, ColumnName55, Column56, ColumnLongName0051, ThisMakesForColumnLongName6, ColumnNameVeryLongHere15, ColumnLongName17, ColumnName18, column00050, TableName40, ColumnLongName19, ColumnName20, ColumnVeryLongName0021, ColumnName22
)
SELECT DISTINCT
CASE
WHEN (tb02.Column01 = NULL
OR tb02.Column01 = '')
AND (tb02.Column02 = NULL
OR tb02.Column02 = '') THEN ''
ELSE CASE
WHEN (tb02.Column01 = NULL
OR tb02.Column01 = '') THEN tb02.Column02
ELSE CASE
WHEN (tb02.Column02 = NULL
OR tb02.Column02 = '') THEN tb02.Column01
ELSE tb02.Column01 +
', ' +
tb02.Column02
END
END
END AS 'Name',
tb02.Col3 'Col3',
tb02.ColuumnLongName4 'Column4#',
tb02.Column5 'Column5',
tb02.ColumnLongName6 'Column6 Label',
tb03.Col7 'Column7 Label',
t05.Col8 'Col8',
CASE
WHEN (tb02.ColumnName24 IS NOT NULL) THEN CONVERT(VARCHAR(15),
tb04.Col10)
ELSE 'Not Specified'
END 'Column10',
CASE
WHEN (t05.Col8 = NULL
AND t06.Col11 = NULL) THEN ''
ELSE CASE
WHEN (t05.Col8 = NULL) THEN t06.Col11
ELSE CASE
WHEN (t06.Col11 = NULL) THEN t05.Col8
ELSE t05.Col8 +
' - ' +
t06.Col11
END
END
END AS 'Column56',
CASE
WHEN (tb07.ColumnVeryLongNameHere12 IS NOT NULL)
-- 'ColumnLongName0051'
THEN tb07.ColumnVeryLongNameHere12
ELSE CASE
WHEN (tb07.ColumnVeryLongNameHere13IS NOT NULL) THEN (SELECT tb022.ColumnLongName14
FROM dbo.VeryLongTableNamehere0000002
tb022 WITH (nolock)
WHERE tb022.ColuumnLongName4 = tb07.ColumnNameVeryLongHere15
AND tb022.Column16 = tb07.Column16)
ELSE NULL
END
END AS 'Long Column Label',
tb07.ThisMakesForColumnLongName6,
tb07.ColumnNameVeryLongHere15,
tb07.ColumnLongName17,
tb02.ColumnName18 'Column Label',
CASE
WHEN (tb07.ColumnVeryLongNameHere12 IS NOT NULL) THEN Datediff(HOUR,tb07.ColumnVeryLongNameHere12,tb02.ColumnName18
) /
24
ELSE ''
END AS 'Column Lab',
CASE
WHEN (tb07.ColumnLongName19 IS NOT NULL) THEN (SELECT tb07.ColumnLongName19
)
ELSE 'Not TableName40'
END AS 'Column 19',
tb07.ColumnLongName19,
tb07.ColumnName20,
tb07.ColumnVeryLongName0021,
tb07.ColumnName22
FROM dbo.VeryLongTableNamehere0000002 tb02 WITH (nolock)

LEFT OUTER JOIN dbo.OhLongTableName03 tb03 WITH (nolock)
ON tb03.ColumnName23 = tb02.ColumnName23
AND tb03.Column16 = tb02.Column16
LEFT OUTER JOIN dbo.TableName00000004 tb04 WITH (nolock)
ON tb04.ColumnName24 = tb02.ColumnName24
AND tb04.Column16 = tb02.Column16
LEFT OUTER JOIN dbo.TableName05 t05 WITH (nolock)
ON t05.Column25 = tb02.Column25
AND t05.Column16 = tb02.Column16
LEFT OUTER JOIN dbo.TableName06 t06 WITH (nolock)
ON t06.Col25 = tb02.Col25
AND t06.Column16 = tb02.Column16
LEFT OUTER JOIN dbo.TableVeryLongName00000000000000007 tb07 WITH (nolock)
ON tb07.ColumnLongName26 = tb02.ColumnLongName26
AND tb07.Column16 = tb02.Column16
WHERE tb02.Column16 = @Parameter01
-- AND tb02.ColumnName27 = 1
AND tb02.ColumnNameHere35 = 0
AND (
(
@Parameter10 = NULL
OR @Parameter10 = ''
)
OR (
UPPER(tb02.ColumnLongName6) LIKE @Parameter10
)
)-- Comment Here
AND tb02.ColumnLongName14 BETWEEN @ParameterLong02 AND @ParameterLong03
AND (
(@Param13= 0 )
OR (
(@Param13= 1 )
AND (
(
@Param14_1 IS NULL
AND @Param14_2 IS NULL
AND @Param14_3 IS NULL
AND @Param14_4 IS NULL
AND @Param14_5 IS NULL
)
OR (
tb02.Column25 IN (@Param14_1, @Param14_2, @Param14_3, @Param14_4, @Param14_5)
)
)
)
OR (
(@Param13= 2 )
AND (
(
@Param14_1 IS NULL
AND @Param14_2 IS NULL
AND @Param14_3 IS NULL
AND @Param14_4 IS NULL
AND @Param14_5 IS NULL
)
OR (tb02.Column25 IS NULL )
OR (
tb02.Column25 NOT IN (Isnull(@Param14_1,0), Isnull(@Param14_2,0), Isnull(@Param14_3,0), Isnull(@Param14_4,0), Isnull(@Param14_5,0))
)
)
)
)
AND (
(@ParameterLong11 = 0 )
OR (
(@ParameterLong11 = 1 )
AND (
(
@Parameter12_1 IS NULL
AND @Parameter12_2 IS NULL
AND @Parameter12_3 IS NULL
AND @Parameter12_4 IS NULL
AND @Parameter12_5 IS NULL
)
OR (
tb02.ColumnName23 IN (@Parameter12_1, @Parameter12_2, @Parameter12_3, @Parameter12_4, @Parameter12_5)
)
)
)
OR (
(@ParameterLong11 = 2 )
AND (
(
@Parameter12_1 IS NULL
AND @Parameter12_2 IS NULL
AND @Parameter12_3 IS NULL
AND @Parameter12_4 IS NULL
AND @Parameter12_5 IS NULL
)
OR (tb02.ColumnName23 IS NULL )
OR (
tb02.ColumnName23 NOT IN (Isnull(@Parameter12_1,0), Isnull(@Parameter12_2,0), Isnull(@Parameter12_3,0), Isnull(@Parameter12_4,0), Isnull(@Parameter12_5,0))
)
)
)
)
AND (
(@ParameterVeryLong15 = 0 )
OR (
(@ParameterVeryLong15 = 1 )
AND (
(
@ParameterLong16_1 IS NULL
AND @ParameterLong16_2 IS NULL
AND @ParameterLong16_3 IS NULL
AND @ParameterLong16_4 IS NULL
AND @ParameterLong16_5 IS NULL
)
OR (
tb02.ColumnName28 IN (@ParameterLong16_1, @ParameterLong16_2, @ParameterLong16_3, @ParameterLong16_4, @ParameterLong16_5)
)
)
)
OR (
(@ParameterVeryLong15 = 2 )
AND (
(
@ParameterLong16_1 IS NULL
AND @ParameterLong16_2 IS NULL
AND @ParameterLong16_3 IS NULL
AND @ParameterLong16_4 IS NULL
AND @ParameterLong16_5 IS NULL
)
OR (tb02.ColumnName28 IS NULL )
OR (
tb02.ColumnName28 NOT IN (Isnull(@ParameterLong16_1,0), Isnull(@ParameterLong16_2,0), Isnull(@ParameterLong16_3,0), Isnull(@ParameterLong16_4,0), Isnull(@ParameterLong16_5,0))
)
)
)
)
AND (
(Isnull(@Parameter6,0) = 0 )
OR (
tb02.Column5 IN (SELECT name
FROM dbo.ColumnName29 WITH (nolock)
WHERE Column5id = @Parameter6)
)
)
AND (
(Isnull(@ParameterVeryLong09,0) = 0 )
OR (
tb07.ColumnLongName19 IN (SELECT name
FROM [dbo].[Some001ColumnLongName19s]
WITH (nolock)
WHERE ColumnLongName19id = @ParameterVeryLong09)
)
)
AND (
(Isnull(@ParameterVeryLong17,0) = 0 )
OR (
tb02.attendingphysicianid = @ParameterVeryLong17
)
)
AND (
Isnull(@Parameter18,0) = 0
OR (
EXISTS (
SELECT 1
FROM dbo.VeryLongTableNamehere0000002 tb022
WITH (nolock)
JOIN dbo.TableNameHere07 TabAlias WITH (nolock)
ON (
TabAlias.Col30 = tb022.finalCol30
AND TabAlias.code = @Parameter18
)
WHERE tb022.ColuumnLongName4 = tb07.ColumnNameVeryLongHere15
AND tb022.Column16 = tb07.Column16
)
)
)
AND (
Isnull(@Parameter19,0) = 0
OR (
EXISTS (
SELECT 1
FROM dbo.VeryLongTableNamehere0000002 tb022
WITH (nolock)
JOIN dbo.TableNameHere07 TabAlias WITH (nolock)
ON (
TabAlias.Col30 = tb022.finalCol30
AND TabAlias.drgyear = @Parameter19
)
WHERE tb022.ColuumnLongName4 = tb07.ColumnNameVeryLongHere15
AND tb022.Column16 = tb07.Column16
)
)
)
END

IF (@ParameterLong5=0 )
SELECT *
FROM @mytable
ELSE
IF (@ParameterLong5=1 )
SELECT *
FROM @mytable
WHERE TableName40 <> 'Stuff Here'
ELSE
IF (@ParameterLong5=2 )
SELECT *
FROM @mytable
WHERE TableName40 = 'Other Stuff'
SET nocount OFF

SET quoted_identifier OFF
SET ansi_nulls ON
GO
GRANT EXECUTE ON [dbo].[ObjectnameHere0001] TO [SomeGroup0000001]
GO
GRANT EXECUTE ON [dbo].[ObjectnameHere0001] TO [SomeGRoup0000002]
GO
GRANT VIEW DEFINITION ON [dbo].[ObjectnameHere0001] TO [Someothergroup00003]
GO

This appears to be known issue with the code wrapping, issue #1229. The formatter (cross-database) treats @ and some other special symbols as valid word separators. A fix for this will be available in the next build, scheduled for release by the end of the week.

New public-release build has been delayed by a few days because we are adding some other changes unrelated to the described issue with the formatter. The issue with @ and other special symbols used in variable and object names and breaking the formatter has been already fixed. If you are interested, I can provide you with a download link for a private build. Or if you prefer, you can wait till a public build become available.

Build 3.5.21 containing fix for this bug is now available for download. Please upgrade.

For more info about this release, things added/fixed, see http://www.softtreetech.com/support/phpBB2/viewtopic.php?p=21157