Parameterized sorting in SQL Server

Applications that allow users to sort data by different columns of the table might need to go for dynamic stored procedures or will end up in adding multiple procedures based on the number of combinations. In SQL Server we can achieve this easily through parameterized sorting.
As shown in the below example we can have a parameter which says the column on which the sort criteria can be applied and using the case statement we achieve the results without going for a dynamic /multiple stored procedures.

DECLARE @SortOrder INT
SET @SortOrder =1
SELECT ForeName,
SurName,
PASID
FROM PAtient
WHERE Forename LIKE 'A%'
ORDER BY CASE WHEN @SortOrder = 1 THEN ForeName
WHEN @SortOrder = 2 THEN SurName
ELSE PASID
END

Generate Index scripts using included columns

This article is a continuation of the previous article http://samsudeenb.blogspot.com/2007/11/scripts-to-drop-and-recreate-indexes-in.html with small enhancement to generate the index scripts with included columns. The below function “fGetIncludedIndex is used to get the included columns for a given index.

CREATE FUNCTION fGetIncludedIndex (
@objname VARCHAR(50),
@indid INT
)
RETURNS NVARCHAR(200)
AS
BEGIN
DECLARE @V_IncludedCol NVARCHAR(200)
select @V_IncludedCol = COALESCE(@V_IncludedCol + ',', '') +
( select name from sys.syscolumns where id =a.Object_id and colid =a.column_id)
from sys.index_columns a
where object_name(a.object_id) =@objname
and a.index_id =@indid
and a.Is_included_column =1
order by a.index_column_id
return @V_IncludedCol
END

After creating the function in the DB make a small change to the existing query( highlighted in bold) to generate the index scripts with included columns

SELECT 'CREATE ' +
CASE IS_UNIQUE WHEN 1 THEN 'UNIQUE ' ELSE ' ' END +
'NONCLUSTERED ' + ' INDEX '+
NAME + ' ' +' ON ' +
OBJECT_NAME (OBJECT_ID) +
'('+
DBO.fGetIndexCols (object_NAME(OBJECT_ID), index_id) + ')'
+Case when dbo.fGetIncludedIndex(object_NAME(OBJECT_ID), index_id) is null then '' else ' INCLUDE ('+dbo.fGetIncludedIndex(object_NAME(OBJECT_ID), index_id)+')' end +
' ON ['+
( SELECT GROUPNAME
FROM SYSFILEGROUPS
WHERE GROUPID = DATA_SPACE_ID
) + ']' IndexScript
FROM SYS.INDEXES
WHERE NAME IS NOT NULL
AND Is_Primary_Key =0
AND type_desc ='NONCLUSTERED'
AND OBJECT_ID > 97

How to take week or month wise report in SQL Server

I was breaking my head whole day for writing a stored procedure which produces week wise report for plotting a revenue trend. I came across this solution which is fairly a simple one. Just thought of sharing this

The below code is written using the SQL Server built in functions DATEDIFF & DATEADD to produce the report

Select DATEADD(WK, datediff(WK, 0, CollectionDate),0) as week,
SUM(Amount)
from Revenue
group by dateadd(WK, datediff(WK 0, CollectionDate),0)
order by 1 asc

The logic here is to find out the corresponding week from Default date (01-01-1900) and adding the default date again to the result will get the corresponding week in date format. The same can be used for generating month wise report as well by just replacing "wk" with "MM"

Select DATEADD(MM, datediff(MM, 0, CollectionDate),0) as week,
SUM(Amount)
from Revenue
group by dateadd(MM, datediff(MM 0, CollectionDate),0)
order by 1 asc

Note: adding “0” will be automatically converted to default date “01-01-1900”