CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2
kyooryu
kyooryu

Reputation: 1509

DROP FUNCTION doesn't work?

I'm trying to create a function and I already have that function in the database. To bypass this I'm using drop function, but I still keep getting 'theres already object named 'getCost' in the database'. What might be the cause?

Query:

USE DWDB_PROPER;
GO
IF OBJECT_ID (N'dbo.getCost', N'TF') IS NOT NULL
DROP FUNCTION dbo.getCost;
GO
CREATE FUNCTION getCost(@person VARCHAR(10), @month TINYINT, @year SMALLINT, @Category    VARCHAR(20))
RETURNS INT 
AS
BEGIN
--function body here

Upvotes: 0

Views: 1068

Answers (2)

praveen
praveen

Reputation: 12271

yours is a scalar function as it is only returning a value so in the if clause you should mention FN instead of TF.Read this

USE DWDB_PROPER;
GO
IF OBJECT_ID (N'dbo.getCost', N'FN') IS NOT NULL
DROP FUNCTION dbo.getCost;
GO
CREATE FUNCTION getCost(@person VARCHAR(10), @month TINYINT, @year SMALLINT, @Category       VARCHAR(20))
RETURNS INT 
AS
BEGIN
--function body here

Your are returning a single value instead of a table .So RETURNS int is a multiselect Scalar Function where as Returns Table is a Table Value function

Upvotes: 6

Akash KC
Akash KC

Reputation: 16310

Try with following query:

USE DWDB_PROPER;
GO
IF EXISTS (
    SELECT * FROM sysobjects WHERE id = object_id(N'dbo.getCost') 
    AND xtype IN (N'FN', N'IF', N'TF')
)
    DROP FUNCTION dbo.getCost
GO
CREATE FUNCTION getCost(@person VARCHAR(10), @month TINYINT, @year SMALLINT, @Category    VARCHAR(20))
RETURNS INT 
AS
BEGIN

Upvotes: 2

Related Questions