Select a.TaskID, a.TaskName, a.TaskCode, a.ProgID,  a.ProjID, a.ContractID, a.Deleted, a.UpdatedBy, rtrim(b.progno) AS progno,  rtrim(c.projno) AS projno,rtrim(d.contractno) AS contractno  from Tab_ccsnetTasks as a Join Tab_ccsNetPrograms as b  ON a.progid = b.progid  JOIN TAB_ccsNetProjects AS c ON a.ProjID = c.ProjID  JOIN TAB_ccsNetContracts AS d ON a.ContractID = d.ContractID   Where 1=1  and b.progid = 0 and c.ProjID = 0 and d.ContractID = 17   

ALTER PROCEDURE [dbo].[USP_GetTasks1]
(
		@ProgID 			int = null,
		@ProjID			int = null,
		@ContractID		int = null

)

AS

declare @sqlwhere varchar(1000)
declare @SQL varchar(4000)

select @SqlWhere = 'Where 1=1 '

if @ProgID is not null 

      select @SqlWhere = @SqlWhere + ' and b.progid = ' +  cast(@ProgID   as nvarchar)



if @ProjID is not null   

      select @SqlWhere = @SqlWhere + ' and c.ProjID = ' +  cast(@ProjID   as nvarchar)


if @ContractID is not null   

      select @SqlWhere = @SqlWhere + ' and d.ContractID = ' +  cast(@ContractID   as nvarchar)


Select @SQL = 'Select a.TaskID, a.TaskName, a.TaskCode, a.ProgID,'

Select @SQL = @SQL + '  a.ProjID, a.ContractID, a.Deleted, a.UpdatedBy, rtrim(b.progno) AS progno,'

Select @SQL = @SQL + '  rtrim(c.projno) AS projno,rtrim(d.contractno) AS contractno'

Select @SQL = @SQL + '  from Tab_ccsnetTasks as a Join Tab_ccsNetPrograms as b  ON a.progid = b.progid'

Select @SQL = @SQL + '  JOIN TAB_ccsNetProjects AS c ON a.ProjID = c.ProjID'

Select @SQL = @SQL + '  JOIN TAB_ccsNetContracts AS d ON a.ContractID = d.ContractID'

select @SQl = @SQL + '   '  + @SqlWhere  + '   ' 

EXEC (@SQL)

quote:

---

Originally posted by reddymade

I changed to this: if isnull(@ProgID,'0') <> 0

Now it is working.

---

select @SqlWhere = @SqlWhere + ' and b.progid = ' +  cast(@ProgID   as nvarchar(length))

quote:

---

Originally posted by reddymade

I changed to this: if isnull(@ProgID,'0') <> 0

Now it is working.

---