TableToString_ListToTable_Use1

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[Usp_GetAllProjectsByLister]
(
@email VARCHAR(50)
) -- [Usp_GetAllProjectsByLister] 'a@a.com'
AS
DECLARE @userid bigint
SET @userid = (Select UserID from tbl_User where Email=@email)
BEGIN

SELECT ID, ProjectTitle,Description,CONVERT(varchar(50),DateCreated,106) as DateCreated,

dbo.TableToString(tbl_Project.CategoryID) as Category,

tbl_project.DaysOpenForBids,

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

CASE
WHEN 0 < DATEDIFF(DAY,GETDATE(),
DateAdd(Day,tbl_project.DaysOpenForBids,tbl_Project.DateCreated))
THEN
CONVERT(varchar,DATEDIFF(DAY,GETDATE() ,
DateAdd(Day,tbl_project.DaysOpenForBids,tbl_Project.DateCreated))) + ' Day(s)'
ELSE
CASE
WHEN 0=DATEDIFF(DAY,GETDATE() ,
DateAdd(Day,tbl_project.DaysOpenForBids,tbl_Project.DateCreated))
THEN
'Today'
ELSE
'0 Day(s)'
END
END
AS DaysOpen
FROM tbl_Project
WHERE UserID=@userid
ORDER BY ID DESC
END
IF @@ERROR <> 0
RETURN -1
ELSE
RETURN 1

Posted in: SQL,SQL UTILITY FUNCTIONS