Function to Format Select Queries to Columns
This function will strip all extra spaces, CRLFs and tabs from a SELECT statement, from the SELECT keyword to the End of the Order By clause, then reformat the statement to put all keywords at the left margin and subordinate clauses indented by ONE Tab. There is an example of a SELECT statement that came from the Enterprise Manager Query Builder Grid, with the Final output shown after that. The function is invoked with: print dbo.ufx_fixcommas(' ') with the Select statement between the single quotes. Any single quoted parameters MUST have TWO single quotes before running the Print command. The function strips the double quotes and leave the single quote in place. I usually use the REPLACE function first to replace ' with '' then add the print dbo.ufx_fixcommas(' before the SELECT keyword then close the SELECT statement with ') This is not as fancy as I would like, but it's a lot quicker than manually formatting SQL statements.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
--* -------------------------------------------------------------
--* NAME: --print dbo.ufx_fixcommas('
--* AUTHOR: Butch Hoffman (hoffman@gatewaycc.edu)
--* DATE: 09/29/2003
--* APPLICATION: ODS Programmer
--* PURPOSE: Convert SQL to Column format
--* ORGANIZATION: GateWay Community College - Phoenix AZ
--* ---------------------------------------------------------------
CREATE FUNCTION dbo.ufx_FixCommas (
@InputStr00 varchar (8000)
)
RETURNS varchar(8000) AS
BEGIN
Declare
@InputStr1 varchar (8000),
@InputStr0 varchar (80),
@InputStr varchar (8000),
@NewStart int,
@NewStart1 int,
@OldLen int,
@CrLf char (2),
@Tab char (1)
SET @CrLf = char(13) + char(10)
SET @Tab = char(9)
-- The next 7 lines strip spaces, tabs and CrLf's from the input string.
set @InputStr00 = REPLACE ( @InputStr00 , @CrLf , ' ' )
set @InputStr00 = REPLACE ( @InputStr00 , @Tab , ' ' )
set @InputStr00 = REPLACE ( @InputStr00 , ' ' , ' ' )
set @InputStr00 = REPLACE ( @InputStr00 , ' ' , ' ' )
set @InputStr00 = REPLACE ( @InputStr00 , ' ' , ' ' )
set @InputStr00 = REPLACE ( @InputStr00 , ' ' , ' ' )
set @InputStr00 = LTrim(Rtrim(@InputStr00))
--print @InputStr00
set @InputStr = @InputStr00
--set @InputStr = 'SELECT [AA_INST], [AA_SID], [AA_NAME], [AA_PREV_NAME], [AA_ALT_INST], [AA_NAME_SORT], [AA_SPEC_NAME_FLAG], [AA_SALUTATION], [AA_PREV_SID], [AA_BIRTH_DT], [AA_SEX], [AA_MARITAL_STAT], [AA_ETHNIC_ORIGIN], [AA_RELIGION], [AA_CITIZENSHIP], [AA_STATE_ORGIN], [AA_COUNTY_ORGIN], [AA_RESIDENCY], [AA_INFO_RELSE_FLAG], [AA_HANDICAP_TYPE], [AA_VISA_TYPE], [AA_VISA_EXP_DT], [AA_FOREIGN_STUDENT], [AA_CO_OP_STUDENT], [AA_VET_CODE], [AA_VET_BENEFIT], [AA_VET_CLAIM_NUM], [AA_VET_BEN_EXP_DT], [AA_GI_CERT_TERM], [AA_GI_CERT_TYPE], [AA_NOK_NAME], [AA_NOK_RELATION], [AA_NOK_PHONE], [AA_ENTRY_DT], [AA_TONGUE], [AA_T2202A_ELIG], [AA_CURSTAT], [AA_LAST_RPT_YR], [AA_LAST_FULL_PART], [AA_LAST_LVL], [AA_LAST_GRAD_DEG], [AA_LAST_GRAD_MAJ], [AA_FEECAT], [AA_FORPOS], [AA_IMSTAT], [AA_BIRTH_CENTURY_FILLER], [AA_SECURITY_GROUP], [AA_PERSONAL_ID], [AA_NCAA_ATHLETE], [AA_NCAA_GPA], [AA_PASSPORT_EXP_DT], [AA_STU_WORK_PHONE], [AA_NICKNAME], [AA_DRAFT_REG], [AA_HOLD_FLAGS], [AA_MM_IND], [AA_BR_IND], [AA_RA_IND], [AA_RB_IND], [AA_RP_IND], [AA_SL_IND], [AA_SITE], [AA_EXCURR_FILLER], [AA_SPOUSE_ID], [AA_ADS_RECORDED], [AA_BD_ARCHIVED_TERM], [AA_SSN], [AA_UID], [AA_UID_ACTION], [AA_UID_ACTION_DATE], [AA_ABILITY_TO_BENEFIT], [AA_HANDICAP_1], [AA_HANDICAP_2], [AA_HANDICAP_3], [AA_ADMISSION], [AA_APP_COMPLETE], [AA_CURR_INTENT], [AA_ELIGIBLE], [AA_ENTRY_YYT], [AA_ENTRY_DAYEVE], [AA_HS_CODE], [AA_HS_YEAR], [AA_HS_GRADUATE], [AA_CUM_MAJ_HOURS], [AA_SSCLAIMNUM], [AA_SKILL_CENTER], [AA_FAMS_CHECKS_RULE], [AA_ORIGIN_SCREEN], [AA_ORIGIN_OPERID], [AA_ORIGIN_DT], [AA_PP_IND], [AA_MAPS_IND], [AA_WRK_PHN_EXT], [AA_APPL_REC], [AA_ENG_PRIM_LANG], [AA_ENG_PL_ASSIST], [AA_HW_ITEMS], [AA_HELP_WITH_OTHER], [AA_FIRST_LANG], [AA_CURR_LANG], [AA_OTHER_LANG_1], [AA_OTHER_LANG_2], [AA_OTHER_LANG_3], [AA_KIOSK_PIN], [AA_BAR_CODE], [AA_ADDRESS_CTR], [AA_MAINT_DT]'
set @OldLen = Len(@InputStr)
set @InputStr1 = ''
set @InputStr0 =
SUBSTRING(@InputStr,1,PATINDEX('% %',@InputStr))
set @NewStart = PATINDEX( '% %' , @InputStr0 )
set @NewStart1 = PATINDEX( '% %' , @InputStr0 )
while @NewStart1 < Len(@InputStr)
Begin
If @InputStr0 = 'SELECT'
Begin
set @InputStr1 = @InputStr1 + RTrim(@InputStr0)
End
Else
If @InputStr0 = 'DISTINCT'or @InputStr0 = 'join'
Begin
set @InputStr1 = @InputStr1 +' '+ RTrim(@InputStr0)
End
Else
If @InputStr0 = '+' or @InputStr0 = 'AS' or @InputStr0 = '='
or @InputStr0 = '<>' or @InputStr0 = '>=' or @InputStr0 = '<='
or @InputStr0 = '<' or @InputStr0 = '>' or @InputStr0 = '/'
or @InputStr0 = 'by' or @InputStr0 = 'like' or @InputStr0 = '*'
Begin
set @InputStr1 = @InputStr1 + ' ' + RTrim(@InputStr0)
End
Else
If @InputStr0 = 'From' or @InputStr0 = 'inner' or @InputStr0 = 'where'
or @InputStr0 = 'order' or @InputStr0 = 'on' or @InputStr0 = 'and'
or @InputStr0 = 'BETWEEN' or @InputStr0 = 'OR'
Begin
set @InputStr1 = @InputStr1 + @CrLf + RTrim(@InputStr0)
End
Else
Begin
set @InputStr1 = @InputStr1 + @CrLf + @Tab + RTrim(LTrim(@InputStr0))
End
set @NewStart = PATINDEX('% %', @InputStr ) + 1
IF PATINDEX('% %',@InputStr)+1 = 1
Begin
set @NewStart1 = @OldLen + 2
End
set @InputStr = LTrim(SUBSTRING(@InputStr,@NewStart,@OldLen))
set @InputStr0 =
SUBSTRING(@InputStr,1,PATINDEX('% %',@InputStr))
set @InputStr0 = LTrim(@InputStr0)
End
set @InputStr1 = RTrim(@InputStr1) + LTrim(@InputStr)
Return @InputStr1
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- End of Function
/*
-- This is one query in need of formating. Note the TWO single quotes ('')
-- around the Hard Coded Parameters. This function strips the TWO back to ONE '.
-- I usually use the Replace function to put the two single quotes in, then
-- add the "print dbo.ufx_fixcommas('" before the SELECT key word,
-- and add the closing ') at the end of the Order By clause.
-- This isn't as perfect as I'd like it, but it generally gets the key words
-- left justified and subordiante clauses are indented by ONE tab.
*//*
print dbo.ufx_fixcommas('SELECT DISTINCT
rc.RC_CRS_PREFIX + rc.RC_CRS_NUMBER + rc.RC_SUFFIX_1 + rc.RC_SUFFIX_2 AS Course, rc.RC_SECTION, rc.RC_SECTION_TITLE,
rcm.RC_SD_BLDG, rcm.RC_SD_ROOM, rc.RC_TALLY_ENR, rcsd.RC_INSTR_NAME, rcm.RC_SD_START_TIME, rcm.RC_SD_STOP_TIME,
rcm.RC_SD_DAYS, rc.RC_START_DT, rc.RC_END_DT, rc.RC_DAY_EVE
FROM GW_RCFILE rc INNER JOIN
GW_RC_SD_MEETS rcm ON
rc.RC_CRS_PREFIX + rc.RC_CRS_NUMBER + rc.RC_SUFFIX_1 + rc.RC_SUFFIX_2 + rc.RC_SECTION = rcm.RC_CRS_PREFIX + rcm.RC_CRS_NUMBER +
rcm.RC_SUFFIX_1 + rcm.RC_SUFFIX_2 + rcm.RC_SECTION INNER JOIN
GW_RC_SD_INSTRUCTORS rcsd ON
rc.RC_CRS_PREFIX + rc.RC_CRS_NUMBER + rc.RC_SUFFIX_1 + rc.RC_SUFFIX_2 + rc.RC_SECTION = rcsd.RC_CRS_PREFIX + rcsd.RC_CRS_NUMBER +
rcsd.RC_SUFFIX_1 + rcsd.RC_SUFFIX_2 + rcsd.RC_SECTION
WHERE (rcm.RC_SD_START_TIME <> ''TBA'') AND (rc.RC_START_DT BETWEEN @what_start AND @what_end) AND (rc.RC_DAY_EVE = @what_de) AND
(rcm.RC_CTF_TERM = @what_term) AND (rcsd.RC_CTF_TERM = @what_term) AND (rc.RC_REG_STATUS <> ''X'') AND (rc.RC_ON_CAMPUS = ''Y'') AND
(rcm.RC_SD_SEQUENCE_COUNTER = ''0'') AND (rcsd.RC_SD_INSTR_SEQUENCE_COUNTER = ''0'')
ORDER BY Course')
*/
-- Formatted output Below:
/*
SELECT DISTINCT
rc.RC_CRS_PREFIX +
rc.RC_CRS_NUMBER +
rc.RC_SUFFIX_1 +
rc.RC_SUFFIX_2 AS
Course,
rc.RC_SECTION,
rc.RC_SECTION_TITLE,
rcm.RC_SD_BLDG,
rcm.RC_SD_ROOM,
rc.RC_TALLY_ENR,
rcsd.RC_INSTR_NAME,
rcm.RC_SD_START_TIME,
rcm.RC_SD_STOP_TIME,
rcm.RC_SD_DAYS,
rc.RC_START_DT,
rc.RC_END_DT,
rc.RC_DAY_EVE
FROM
GW_RCFILE
rc
INNER JOIN
GW_RC_SD_MEETS
rcm
ON
rc.RC_CRS_PREFIX +
rc.RC_CRS_NUMBER +
rc.RC_SUFFIX_1 +
rc.RC_SUFFIX_2 +
rc.RC_SECTION =
rcm.RC_CRS_PREFIX +
rcm.RC_CRS_NUMBER +
rcm.RC_SUFFIX_1 +
rcm.RC_SUFFIX_2 +
rcm.RC_SECTION
INNER JOIN
GW_RC_SD_INSTRUCTORS
rcsd
ON
rc.RC_CRS_PREFIX +
rc.RC_CRS_NUMBER +
rc.RC_SUFFIX_1 +
rc.RC_SUFFIX_2 +
rc.RC_SECTION =
rcsd.RC_CRS_PREFIX +
rcsd.RC_CRS_NUMBER +
rcsd.RC_SUFFIX_1 +
rcsd.RC_SUFFIX_2 +
rcsd.RC_SECTION
WHERE
(rcm.RC_SD_START_TIME <>
'TBA')
AND
(rc.RC_START_DT
BETWEEN
@what_start
AND
@what_end)
AND
(rc.RC_DAY_EVE =
@what_de)
AND
(rcm.RC_CTF_TERM =
@what_term)
AND
(rcsd.RC_CTF_TERM =
@what_term)
AND
(rc.RC_REG_STATUS <>
'X')
AND
(rc.RC_ON_CAMPUS =
'Y')
AND
(rcm.RC_SD_SEQUENCE_COUNTER =
'0')
AND
(rcsd.RC_SD_INSTR_SEQUENCE_COUNTER =
'0')
ORDER BY Course
*/ 
