{"id":1108,"date":"2011-09-27T04:42:31","date_gmt":"2011-09-27T09:42:31","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?p=1108"},"modified":"2011-10-14T21:09:15","modified_gmt":"2011-10-15T02:09:15","slug":"sql-formatter-option-subquery-newline-after-exists-keyword","status":"publish","type":"post","link":"https:\/\/www.dpriver.com\/blog\/2011\/09\/sql-formatter-option-subquery-newline-after-exists-keyword\/","title":{"rendered":"SQL Formatter Option: Subquery newline after EXISTS keyword"},"content":{"rendered":"

This format option control whether put a blank line after EXISTS keyword when there is a subquery follows.<\/p>\n

name: Subquery_Newline_After_EXISTS, default is false<\/p>\n

setting path: subquery -> Linebreak after EXISTS keyword<\/p>\n

\nSELECT<\/span> TOP<\/span> 1<\/span> CASE<\/span>
\n
WHEN<\/span> CASE<\/span>
\n
WHEN<\/span> NOT<\/span> EXISTS<\/span> (<\/span>SELECT<\/span> [lpa_l1]<\/span>.<\/span>[Categoryid]<\/span>
\n
FROM<\/span> (<\/span>SELECT<\/span> p<\/span>.<\/span>[Categoryid]<\/span> AS<\/span> [categoryid]<\/span>,<\/span>
\n
p<\/span>.<\/span>[Discontinued]<\/span>,<\/span>
\n
p<\/span>.<\/span>[Unitsonorder]<\/span>
\n
FROM<\/span> [northwind]<\/span>.<\/span>[dbo]<\/span>.<\/span>[PRODUCTS]<\/span> p<\/span>
\n
WHERE<\/span> (<\/span>(<\/span> p<\/span>.<\/span>[Categoryid]<\/span> =<\/span> @p1<\/span> )<\/span>)<\/span>)<\/span> [lpa_l1]<\/span>)<\/span> THEN<\/span> 1<\/span>
\n
ELSE<\/span> 0<\/span>
\n
END<\/span> =<\/span> 1<\/span> THEN<\/span> @p3<\/span>
\n
ELSE<\/span> @p5<\/span>
\n
END<\/span> AS<\/span> [llblv_1]<\/span>
\n
FROM<\/span> [northwind]<\/span>.<\/span>[dbo]<\/span>.<\/span>[PRODUCTS]<\/span>;<\/span>
\n<\/span><\/div>\n

After set Subquery_Newline_After_EXISTS to true, SQL will be formatted like this:<\/p>\n

\nSELECT<\/span> TOP<\/span> 1<\/span> CASE<\/span>
\n
WHEN<\/span> CASE<\/span>
\n
WHEN<\/span> NOT<\/span> EXISTS<\/span>
\n
(<\/span>SELECT<\/span> [lpa_l1]<\/span>.<\/span>[Categoryid]<\/span>
\n
FROM<\/span> (<\/span>SELECT<\/span> p<\/span>.<\/span>[Categoryid]<\/span> AS<\/span> [categoryid]<\/span>,<\/span>
\n
p<\/span>.<\/span>[Discontinued]<\/span>,<\/span>
\n
p<\/span>.<\/span>[Unitsonorder]<\/span>
\n
FROM<\/span> [northwind]<\/span>.<\/span>[dbo]<\/span>.<\/span>[PRODUCTS]<\/span> p<\/span>
\n
WHERE<\/span> (<\/span>(<\/span> p<\/span>.<\/span>[Categoryid]<\/span> =<\/span> @p1<\/span> )<\/span>)<\/span>)<\/span> [lpa_l1]<\/span>)<\/span> THEN<\/span> 1<\/span>
\n
ELSE<\/span> 0<\/span>
\n
END<\/span> =<\/span> 1<\/span> THEN<\/span> @p3<\/span>
\n
ELSE<\/span> @p5<\/span>
\n
END<\/span> AS<\/span> [llblv_1]<\/span>
\n
FROM<\/span> [northwind]<\/span>.<\/span>[dbo]<\/span>.<\/span>[PRODUCTS]<\/span>;<\/span>
\n<\/span><\/div>\n","protected":false},"excerpt":{"rendered":"

This format option control whether put a blank line after EXISTS keyword when there is a subquery follows. name: Subquery_Newline_After_EXISTS, default is false setting path: subquery -> Linebreak after EXISTS keyword SELECT TOP 1 CASE WHEN CASE WHEN NOT EXISTS (SELECT [lpa_l1].[Categoryid] FROM (SELECT p.[Categoryid] AS [categoryid], p.[Discontinued], p.[Unitsonorder] FROM [northwind].[dbo].[PRODUCTS] p WHERE (( p.[Categoryid] = @p1 ))) [lpa_l1]) THEN 1 ELSE 0 END = 1 THEN @p3 ELSE @p5 END AS [llblv_1] FROM [northwind].[dbo].[PRODUCTS]; After set Subquery_Newline_After_EXISTS to true, SQL will be formatted like this: SELECT TOP 1 CASE WHEN CASE WHEN NOT EXISTS (SELECT [lpa_l1].[Categoryid] FROM (SELECT p.[Categoryid] AS [categoryid], p.[Discontinued], p.[Unitsonorder] FROM [northwind].[dbo].[PRODUCTS] p WHERE (( p.[Categoryid] = @p1 ))) [lpa_l1]) THEN 1 ELSE 0 END = 1 THEN @p3 ELSE @p5 END AS [llblv_1] FROM [northwind].[dbo].[PRODUCTS]; <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[18,24,22],"tags":[],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\nSQL Formatter Option: Subquery newline after EXISTS keyword<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n