Hi ,
from my c# code I am getting below error when i try to call my procedure.
I tried to debug it but not getting where is the mistake is exactly ?
{Violation of PRIMARY KEY constraint PK__#B12AD42__19093A2B4B4AF11E. Cannot insert duplicate key in object dbo.@TCategory. The duplicate key value is (0).\r\nThe statement has been terminated.}
ALTER PROCEDURE [dbo].[spGetAllProjectAndTaskForUser]
@EmpID INT
, @UserID int
, @ProjectName NVARCHAR(MAX)
, @Status XML
, @Category XML
, @Priority XML
, @Location XML
, @IsAdvanceSearch BIT = NULL
, @StartDate DateTime
, @EndDate DateTime
, @CreUserEmpID INT
AS
SET NOCOUNT ON
BEGIN
DECLARE @TStatus TABLE(StatusID INT PRIMARY KEY)
DECLARE @TCategory TABLE(CategoryID INT PRIMARY KEY)
DECLARE @TPriority TABLE(PriorityID INT PRIMARY KEY)
IF (@Status IS NOT NULL)
BEGIN
INSERT INTO @TStatus(StatusID)
SELECT DataTable.ItemCol.value('C1[1]','int')
FROM @Status.nodes('//T/R') AS DataTable(ItemCol)
END
ELSE
BEGIN
INSERT INTO @TStatus(StatusID)
SELECT DISTINCT l.LookupID FROM [Lookup] l
INNER JOIN LookupType lt ON l.Type = lt.LookupTypeID
WHERE lt.Code = 'TaskStatus'
END
IF (@Category IS NOT NULL)
BEGIN
INSERT INTO @TCategory(CategoryID)
SELECT DataTable.ItemCol.value('C1[1]','int')
FROM @Category.nodes('//T/R') AS DataTable(ItemCol)
END
ELSE
BEGIN
INSERT INTO @TCategory(CategoryID)
SELECT ToDoCategoryId From ToDoCategory
END
IF (@Priority IS NOT NULL)
BEGIN
INSERT INTO @TPriority(PriorityID)
SELECT DataTable.ItemCol.value('C1[1]','int')
FROM @Priority.nodes('//T/R') AS DataTable(ItemCol)
END
ELSE
BEGIN
INSERT INTO @TPriority(PriorityID)
SELECT DISTINCT l.LookupID FROM [Lookup] l
INNER JOIN LookupType lt ON l.Type = lt.LookupTypeID
WHERE lt.Code = 'TaskPriority'
END
SELECT
('P' + CAST(p.ProjectID as Varchar(10))) AS ID,
p.ProjectID,
0 AS TaskID,
p.Name,
p.Detail,
p.StartDate,
p.EndDate,
GetDate() AS AlertDate,
p.PriorityId as PriorityId,
lp.Type as PriorityType,
lp.Code as PriorityCode,
lp.Text as PriorityText,
p.StatusId as StatusId,
ls.Type as StatusType,
ls.Code AS StatusCode,
ls.Text AS StatusText,
p.CategoryID as CategoryID,
lc.ToDoCategoryID as CategoryType,
lc.Code AS CategoryCode,
lc.Name AS CategoryText,
p.Pecentage AS Complete,
null AS Weightage,
null AS ParentID ,
0 AS Type ,
p.CustomerID,
c.FirstName + ' ' + c.LastName AS CustomerName,
p.CustomerLocationID,
l.Name AS LocationName,
ISNULL(c.FirstName,'') + ' ' + ISNULL(c.LastName,'') + ' ' + ISNULL(l.Name,'') AS Assosiation
FROM Project p
INNER JOIN [ToDoCategory] lc on p.CategoryID = lc.ToDoCategoryID
INNER JOIN [Lookup] ls on p.StatusId = ls.LookupID
INNER JOIN [Lookup] lp on p.PriorityId = lp.LookupID
INNER JOIN @TCategory lct ON p.CategoryID = lct.CategoryID
INNER JOIN @TStatus lcs ON p.StatusId = lcs.StatusID
INNER JOIN @TPriority lcp ON p.PriorityID = lcp.PriorityID
LEFT OUTER JOIN Customer c on p.CustomerID = c.CustomerID
LEFT OUTER JOIN CustomerLocation l on p.CustomerLocationID = l.CustomerLocationID
WHERE p.EmployeeID = @EmpID AND p.IsInActive = 0
AND (@ProjectName IS NULL OR (p.Name LIKE '%' + @ProjectName + '%' OR p.Detail LIKE '%' + @ProjectName + '%'))
AND (@StartDate IS NULL OR p.StartDate >= @StartDate)
AND (@EndDate IS NULL OR (p.EndDate IS NULL OR p.EndDate <= @EndDate))
AND (((@IsAdvanceSearch IS NULL OR @IsAdvanceSearch = 0) AND p.Pecentage < 100) OR @IsAdvanceSearch = 1 )
UNION ALL
SELECT ('T' + CAST(t.TaskId as Varchar(10))) AS ID,t.ProjectID AS ProjectID,t.TaskId,
t.Name,t.Notes,t.StartDate,t.EndDate,AlertDate,
t.PriorityId,lp.Type as PriorityType,lp.Code AS PriorityCode,lp.Text AS PriorityText,
t.StatusId,ls.Type AS StatusType,ls.Code AS StatusCode,ls.Text AS StatusText,
0 as CategoryID, '' as CategoryType, '' AS CategoryCode,'' AS CategoryText,
t.Pecentage AS Complete, t.Weigtage AS Weightage, ('P' + CAST(p.ProjectID as Varchar(10))) As ParentID, 1 AS Type,
0 AS CustomerID, '' AS CustomerName,
0 AS LocationID, '' AS LocationName, '' AS Assosiation
FROM Project p
INNER JOIN Task t on t.ProjectId = p.ProjectId
INNER JOIN [Lookup] lp on t.PriorityId = lp.LookupID
INNER JOIN [Lookup] ls on t.StatusId = ls.LookupID
INNER JOIN @TCategory lct ON p.CategoryID = lct.CategoryID
INNER JOIN @TStatus lcs ON t.StatusId = lcs.StatusID
INNER JOIN @TPriority lcp ON t.PriorityID = lcp.PriorityID
WHERE p.EmployeeID = @EmpID AND p.IsInActive = 0 AND t.IsInActive = 0
AND (@ProjectName IS NULL OR (p.Name LIKE '%' + @ProjectName + '%' OR p.Detail LIKE '%' + @ProjectName + '%'))
AND (@StartDate IS NULL OR p.StartDate >= @StartDate)
AND (@EndDate IS NULL OR (p.EndDate IS NULL OR p.EndDate <= @EndDate))
AND (((@IsAdvanceSearch IS NULL OR @IsAdvanceSearch = 0) AND p.Pecentage < 100) OR @IsAdvanceSearch = 1 )
END
GO