ALTER a FUNCTION Example

In This Topic

Functions and Stored Procedures may be modified when in a database by using the SQL ALTER statement.

First create a function like this:

CREATE FUNCTION [MyRound] 
(
    @Operand Decimal,
    @Places Int
) 
RETURNS DECIMAL 
AS 
BEGIN 
    DECLARE @x decimal; 
    DECLARE @i int; 
    set @x = @Operand * power(10,@Places); 
    set @i = @x; 
    set @x = @i + iif((@x - @i) >= .5,1,0); 
    set @x = @x / power(10,@Places); 
    RETURN(@x); 
END

Now you can call that function and use it like normal. But if you want to update it you have to either use the Data Builder, or write an alter statement like this:

ALTER FUNCTION	Copy Code
ALTER FUNCTION [MyRound] ( @Operand Decimal, @Places Int ) RETURNS DECIMAL AS BEGIN DECLARE @x decimal; DECLARE @i int; set @x = @Operand * power(10,@Places); set @i = @x; set @x = @i + iif((@x - @i) >= .75,1,0); set @x = @x / power(10,@Places); RETURN(@x); END

ALTER FUNCTION

Copy Code

ALTER FUNCTION [MyRound] 
(
    @Operand Decimal,
    @Places Int
) 
RETURNS DECIMAL 
AS 
BEGIN 
    DECLARE @x decimal; 
    DECLARE @i int; 
    set @x = @Operand * power(10,@Places); 
    set @i = @x; 
    set @x = @i + iif((@x - @i) >= .75,1,0); 
    set @x = @x / power(10,@Places); 
    RETURN(@x); 
END