ALTER PROCEDURE[dbo].[Wms_pProject_GetByCriteria]
-- Add the parameters for the stored procedure here
@IsFullSearch bit = null,
@ProjectNo nvarchar(50) = null,
@CustomerId bigint = null,
@ProjectTypeId bigint=null,
@StartDate datetime = null,
@EndDate datetime = null,
@StatusId bigint=null,
@BranchId bigint=null,
@OrderBy nvarchar(max) = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @sql
nvarchar(max),
@sSymbol
nvarchar(max),
@paramlist nvarchar(max);
SELECT @sql =
+'
SELECT
t1.*, '
+' t2.Timestamp AS ProjectTypeTimestamp, t2.Name as ProjectTypeName, '
+' t3.Name AS ProjectStatusName ,'
+' t4.Code as CustomerCode, t4.Name AS CustomerName, t4.Timestamp AS CustomerTimestamp,'
+' t5.Uid as CreatedByUserUid,'
+' t6.Uid as UpdatedByUserUid,'
+' t7.Name AS CreatedByStaffName, t7.IC AS CreatedByStaffIC,t7.EmployeeNo AS CreatedByStaffEmployeeNo,'
+' t8.Name AS UpdatedByStaffName, t8.IC AS UpdatedByStaffIC,t8.EmployeeNo as UpdatedByStaffEmployeeNo,'
+' t9.Name as BranchName,t9.Code as BranchCode'
+' FROM
Wms_tProject t1 '
+' INNER JOIN Wms_tProjectType t2 ON t1.ProjectTypeId = t2.Id '
+' INNER JOIN Wms_tProjectStatus t3 ON t1.StatusId = t3.Id '
+' INNER JOIN Master_tCompany t4 ON t1.CustomerId = t4.Id '
+' INNER JOIN dbo.Auth_tUser AS t5 ON t1.CreatedByUserId = t5.Id '
+' INNER JOIN dbo.Auth_tUser AS t6 ON t1.UpdatedByUserId = t6.Id '
+' left outer
JOIN dbo.Master_tStaff AS t7 ON t1.CreatedByStaffId = t7.Id '
+' left outer
JOIN dbo.Master_tStaff AS t8 ON t1.UpdatedByStaffId = t8.Id '
+' inner join
Master_tCompany t9 ON t1.BranchId = t9.Id '
+' WHERE 1=1 '
IF @IsFullSearch IS NOT NULL
BEGIN
IF @IsFullSearch = 1
BEGIN
SELECT @sSymbol = '%';
END
ELSE
BEGIN
SELECT @sSymbol = '';
END
END
ELSE
BEGIN
SELECT @sSymbol = '%';
END
IF @ProjectNo IS NOT NULL AND LEN(@ProjectNo) > 0
BEGIN
SELECT @ProjectNo = @sSymbol + @ProjectNo + '%'
SELECT @sql = @sql + ' and t1.ProjectNo like @ProjectNo '
END
IF @CustomerId IS NOT NULL AND LEN(@CustomerId) > 0
BEGIN
SELECT @sql = @sql + ' and t1.CustomerId = @CustomerId '
END
IF @ProjectTypeId IS NOT NULL AND LEN(@ProjectTypeId) > 0
BEGIN
SELECT @sql = @sql + ' and t1.ProjectTypeId = @ProjectTypeId '
END
IF @StartDate IS NOT NULL
BEGIN
SELECT @sql = @sql + ' and t1.StartDate >= @StartDate '
END
IF @StartDate IS NOT NULL
BEGIN
SELECT @sql = @sql + ' and t1.EndDate <= @EndDate '
END
IF @StatusId IS NOT NULL
BEGIN
SELECT @sql = @sql + ' and t1.StatusId = @StatusId '
END
IF @BranchId IS NOT NULL
BEGIN
SELECT @sql = @sql + ' and t1.BranchId = @BranchId '
END
IF @OrderBy IS NOT NULL and len(@OrderBy) > 0
BEGIN
SELECT @sql = @sql + ' order by ' + @OrderBy;
END
-- EXECUTE SQL
SELECT @paramlist = '@ProjectNo nvarchar(50)'
+',@CustomerId bigint'
+',@ProjectTypeId bigint'
+',@StartDate datetime'
+',@EndDate datetime'
+',@StatusId bigint'
+',@BranchId bigint';
EXEC sp_executesql @sql, @paramlist, @ProjectNo,@CustomerId,@ProjectTypeId,@StartDate,@EndDate,@StatusId,@BranchId
END
