Apr 27, 2014

How to create sql function in sql server management studio (SSMS)

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