Today I had the need to have optional parameters in my SQL stored procedure. And It wasn't that complex to implement :)
Below is the code:
ALTER PROCEDURE [dbo].[GetSampleRequests]
@Org varchar(50),
@Type varchar(50),
@From DateTime,
@To DateTime
AS
BEGIN
SELECT
Organisation,
ServiceType,
COUNT(DataStatus) AS DataStatusCount
FROM
SampleLog
WHERE
((@From IS NULL OR @To IS NULL) OR
TransactionDateTime BETWEEN @From and @To) AND
(@Org IS NULL OR Organisation = @Org) AND
(@Type IS NULL OR ServiceType = @Type)
GROUP BY
Organisation,
ServiceType
ORDER BY
Organisation,
ServiceType
END
The magic happens when we check whether the parameter is IS NULL :)
Recently I had the necessity to do cross-tab report with the SQL Server data. First thin that comes into everybody's mind is to use the PIVOT functionality in SQL Server. But, being a programmer, I am not that fluent in writing and managing complex queries. I was looking out for some .NET implementation and found an excellent article here which helped me accomplish this task :)
I think this is a lot easier for me than writing SQL queries ;)
And here is performance comparison of .NET CrossTabs and SQL Server CrossTabs