August 27, 2013 at 2:15 am
Hi all,
I have created a function but I am getting an error:
Cannot perform alter on 'dbo.fn_CLCDeviationRequestDetails' because it is an incompatible object type.
Here is the code below:
Can you please suggest whats wrong in this?
Alter function [dbo].fn_CLCDeviationRequestDetails
(
@DevDateChanged DATETIME
)
RETURNS @temptable TABLE
(
ProjectID INT,
ProposalID INT,
SolutionID INT,
UnitID INT,
DeviationNO nvarchar(128),
IsRevisedRequest Bit,
DeviationStatus INT,
RequestDate DATETIME,
RequestBy nvarchar(55),
RepliedBy nvarchar(55)
)
AS
BEGIN
;WITH CTE AS
(
SELECT
DISTINCT D.ProjectId,
D.ProposalId,
D.SolutionId,
D.UnitId,
D.RequestId,
D.DeviationNo,
I.IsRevisedRequest,
D.DeviationStatus,
D.RequestDate,
D.RequestedBy,
D.RepliedBy
FROM DeviationRequestDetails D
JOIN CLCProcessUnitDetailsCompany C ON C.ProjectID = D.ProjectId AND D.ProposalId = C.ProposalID
AND C.SolutionID = D.SolutionId AND C.UnitID = D.UnitId
JOIN DeviationRequestDetailsInstallation I ON C.ProjectID = I.ProjectId
AND I.ProposalId = C.ProposalID
AND C.SolutionID = I.SolutionId AND C.UnitID = I.UnitId
WHERE C.DevDateChanged >= @DevDateChanged
)
INSERT INTO @temptable
SELECT
TOP 1 C.ProjectId,
C.ProposalId,
C.SolutionId,
C.UnitId,
C.DeviationNo,
C.IsRevisedRequest,
C.DeviationStatus,
C.RequestDate,
C.RequestedBy,
C.RepliedBy
FROM CTE C
ORDER BY C.RequestId DESC
RETURN
END
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 27, 2013 at 2:20 am
Oops, my previous existing function was not a multi-statement table-valued function.
I find that ALTER cannot be used to change the function type.
So, I DROP and CREATE the function again and it works :-):-P
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 27, 2013 at 2:24 am
I can't reproduce your error on my system. I'm able to created the function with your code (changing the ALTER to CREATE). I'm also able to alter this function with the code you provided.
Can you DROP and re-CREATE the function?
Edited: I see you have allready figured it out.
August 27, 2013 at 2:40 am
HanShi (8/27/2013)
I can't reproduce your error on my system. I'm able to created the function with your code (changing the ALTER to CREATE). I'm also able to alter this function with the code you provided.Can you DROP and re-CREATE the function?
Edited: I see you have allready figured it out.
Actually when first time I created this function it was not multi statement table valued fuction, then I was trying to alter this function into a multi statement table valued function without droping thats why I was getting that error...
I find in BOL that we can't change the type of function using ALTER. WE need to drop the existing function and then recreate that function.
I was not aware of this thing, I follow this thing and my problem gets solved :-):-P
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply