Scalar-valued Functions in SQL Server

Problem:
The new reporting features in SQL Server 2005 is very useful to generate a report, but putting the logic in the report is not as easy as in a regular aspx page. For example, I want to display some text in a new column depending on the values stored in the other columns in the database. I want it to be sortable in the report as well. This is where the Scalar-valued Functions in SQL Server comes in. Instead putting the logic in the report, it is much better putting it in the SQL Server.

Solution:
Scalar-valued Functions in SQL Server allows DBA to create function to be called in views or stored procedures. The function can contains very complex logic to determine the output. The example below determines the text to be returned depends on the values of the fields. If season=summer and cloud=none, then it’s sunny.


USE [DB_Name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[svf_Weather]
{
@Weather_Date smalldatetime — input parameter
}

RETURNS varchar(20)

AS
BEGIN
DECLARE @Weather varchar(20), @Season varchar(20), @Cloud varchar(20)

— Assigned values to variables
SELECT @Season = [Season], @Cloud = [Cloud]
FROM [DB_Name].[dbo].[qry_view_or_table]
WHERE [Weather_Date] = @Weather_Date

— The logic to determine the returned text
SELECT @Weather = CASE
WHEN @Season = ‘summer’ AND @Cloud = ‘none’ THEN ‘sunny’
WHEN @Season = ‘winter’ AND @Cloud = ‘cloudy’ THEN ‘freezing’
ELSE ‘others’
END
RETURN @Weather
END

Advertisements

No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: