Technical Article

Show LF, CR , Space and Tabs in Stored Procedure

,

Word can do that. Why SQL not? Sometimes I want to see every space, horizontal tabulation, line feed or carriage return. It can help to write well formated store procedures.

/*********************************************************
        original                        replaced with
        ---------------------------------------------
char(10) LINE FEED        »  char(187)
char(13) CARRIAGE RETURN¶  char(182)
char(9)  HORIZONTAL TABULATION—  char(151)
char(32) SPACE·  char(183)

This sample use Windows code page 1251
*********************************************************/USE Northwind
SELECT  REPLACE(
REPLACE(
replace(
replace(TEXT ,char(32),CHAR(183)),
char(9),char(151)) ,
CHAR(10),CHAR(187)),
CHAR(13),CHAR(182)+ CHAR(13))
FROM syscomments where object_name(id) ='SalesByCategory'

/*
result ...

CREATE·PROCEDURE·SalesByCategory¶
»····@CategoryName·nvarchar(15),·@OrdYear·nvarchar(4)·=·'1998'¶
»AS¶
»IF·@OrdYear·!=·'1996'·AND·@OrdYear·!=·'1997'·AND·@OrdYear·!=·'1998'·¶
»BEGIN¶
»—SELECT·@OrdYear·=·'1998'¶
»END¶
Ȧ
»SELECT·ProductName,¶
»—TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2),·OD.Quantity·*·(1-OD.Discount)·*·OD.UnitPrice)),·0)¶
»FROM·[Order·Details]·OD,·Orders·O,·Products·P,·Categories·C¶
»WHERE·OD.OrderID·=·O.OrderID·¶
»—AND·OD.ProductID·=·P.ProductID·¶
»—AND·P.CategoryID·=·C.CategoryID¶
»—AND·C.CategoryName·=·@CategoryName¶
»—AND·SUBSTRING(CONVERT(nvarchar(22),·O.OrderDate,·111),·1,·4)·=·@OrdYear¶
»GROUP·BY·ProductName¶
»ORDER·BY·ProductName¶
»

*/

Rate

Share

Share

Rate