TableToString_ListToTable_Use2

ALTER PROCEDURE [dbo].[Usp_GetAllProjects_ByCategory]
(
@categoryid bigint
)

AS -- [Usp_GetAllProjects_ByCategory] '34'

BEGIN

SELECT tbl_Project.ID,tbl_User.UserName,
CASE
WHEN LEN(tbl_Project.ProjectTitle) > 150
THEN SUBSTRING(tbl_Project.ProjectTitle,0,150) + '...'
ELSE
CASE
WHEN LEN(tbl_Project.ProjectTitle) < 150
THEN tbl_Project.ProjectTitle
ELSE
tbl_Project. ProjectTitle
END
END
AS ProjectTitle,

CONVERT(varchar(50), tbl_Project.DateCreated,106) as DateCreated,

dbo.TableToString(tbl_Project.CategoryID) as Category,

(select count(*) from tbl_Bid where tbl_Bid.ProjectID = tbl_Project.ID ) as Bids

FROM tbl_Project inner join tbl_User
on tbl_Project.UserID = tbl_User.UserID
where tbl_Project.IsActive=1 and tbl_Project.ProjectStatus='Open'

AND
EXISTS (select entry from ListToTable(tbl_Project.CategoryID)where entry=@categoryid)
ORDER BY ID DESC

END

IF @@ERROR <> 0
RETURN -1
ELSE
RETURN 1

Posted in: SQL,SQL UTILITY FUNCTIONS