In
sql server it is very easy to create function. We can create a function in sql
server management studio (SSMS) in following way:
Creating
scalar-valued function using server management studio (SSMS):
Step 1: Expand your
database name node in SSMS. Then expand Programmability node as
shown in the following screenshot:
Step 2: Right click on
Functions node and click on New -> Scalar-valued Function...
Step
3: It will generate following sql script in new query page:
--
================================================
-- Template
generated from Template Explorer using:
-- Create Scalar
Function (New Menu).SQL
--
-- Use the
Specify Values for Template Parameters
-- command
(Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of
comments will not be included in
-- the
definition of the function.
--
================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--
=============================================
-- Author: <Author,,Name>
-- Create date:
<Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName>
(
-- Add the parameters for the
function here
<@Param1, sysname, @p1> <Data_Type_For_Param1,
, int>
)
RETURNS <Function_Data_Type,
,int>
AS
BEGIN
-- Declare the return variable here
DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>
-- Add the T-SQL statements to
compute the return value here
SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>
-- Return the result of the function
RETURN <@ResultVar, sysname, @Result>
END
GO
Step
4: Now edit above script like stored procedure name, parameters, procedure body
etc according to your requirement. For example:
--
=============================================
-- Author: exacthelp.com
-- Create date:
04/27/2014
-- Description: My first scalar valued function
--
=============================================
CREATE FUNCTION My_First_Function
(
-- Add the parameters for the
function here
@ntParameter AS INTEGER
)
RETURNS INTEGER
AS
BEGIN
-- Declare the return variable here
DECLARE @Value AS INTEGER
-- Add the T-SQL statements to
compute the return value here
SELECT @Value = @ntParameter * 10
-- Return the result of the function
RETURN @Value
END
GO
Step
5: To create My_First_Function function press F5 button of your keyboard or
click on Execute button in SSMS.
Step
6: After creating it will be stored under Programmability ->Functions
->Scalar-Valued-Functions node. To re-open or edit it expand programmability node
and right click on your function name and choose modify option.
You
will get the script of your stored procedure. In this example it will be script
of My_First_Function. This will be something like this:
USE
[ExactHelp]
GO
/******
Object: UserDefinedFunction
[dbo].[My_First_Function] Script Date:
04/27/2014 00:42:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--
=============================================
-- Author: exacthelp.com
-- Create date:
04/27/2014
-- Description: My first scalar valued function
--
=============================================
ALTER FUNCTION [dbo].[My_First_Function]
(
-- Add the parameters for the
function here
@ntParameter AS INTEGER
)
RETURNS INTEGER
AS
BEGIN
-- Declare the return variable here
DECLARE @Value AS INTEGER
-- Add the T-SQL statements to
compute the return value here
SELECT @Value = @ntParameter * 10
-- Return the result of the function
RETURN @Value
END
Step 7: You can modify you script and to save it, press F5 button.
No comments:
Post a Comment