写着觉得有点天马行空、不着边际了,其实这个话题有点大,而且和个人习惯、审美观有莫大联系,所谓众口难调,不过有几点应该是一致的:
1:书写脚本的时候,多用空格、Tab键,不要让代码拥挤,杂糅在一起。
2:让代码看起来觉得舒服,一目了然,不要一看就觉得头痛,要细细看上好久,才了解逻辑结构
3:让代码看起来整洁、优美。凌乱不堪是大忌。
4:总结、学习一些书写漂亮的格式。
限于篇幅,下面给出一些我见过的、写的比较优雅的脚本,大家也可以贴出自己写得优美的代码,一起学习探讨。
Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->-- Construct column list SET @sql = N'SET @result = ' + @newline + N' STUFF(' + @newline + N' (SELECT N'','' + ' + N'QUOTENAME(pivot_col) AS [text()]' + @newline + N' FROM (SELECT DISTINCT(' + @on_cols + N') AS pivot_col' + @newline + N' FROM' + @query + N') AS DistinctCols' + @newline + N' ORDER BY pivot_col' + @newline + N' FOR XML PATH('''')),' + @newline + N' 1, 1, N'''');'
EXEC sp_executesql @stmt = @sql, @params = N'@result AS NVARCHAR(MAX) OUTPUT', @result = @cols OUTPUT;
-- Create the PIVOT query SET @sql = N'SELECT *' + @newline + N'FROM' + @newline + N' ( SELECT ' + @newline + N' ' + @on_rows + N',' + @newline + N' ' + @on_cols + N' AS pivot_col,' + @newline + N' ' + @agg_col + N' AS agg_col' + @newline + N' FROM ' + @newline + N' ' + @query + @newline + N' ) AS PivotInput' + @newline + N' PIVOT' + @newline + N' ( ' + @agg_func + N'(agg_col)' + @newline + N' FOR pivot_col' + @newline + N' IN(' + @cols + N')' + @newline + N' ) AS PivotOutput;' |
Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->DECLARE @schemaname AS NVARCHAR(128), @tablename AS NVARCHAR(128), @colname AS NVARCHAR(128), @sql AS NVARCHAR(805);
SET @schemaname = N'dbo'; SET @tablename = N'Orders'; SET @colname = N'CustomerID'; SET @sql = N'SELECT COUNT(DISTINCT ' + QUOTENAME(@colname) + N') FROM ' + QUOTENAME(@schemaname) + N'.' + QUOTENAME(@tablename) + N';';
EXEC(@sql); |