T-SQL Query Example

declare @drugname int =null
declare @clientid  int=null
declare @mfgid int=NULL

DECLARE @Skip INT
DECLARE @Take INT    
DECLARE @SQL VARCHAR(MAX)


declare @q varchar(max)='SELECT * FROM (SELECT ROW_NUMBER() OVER
(ORDER BY drugid asc) rownumber,*,(select unitname from UnitDetails where UnitDetails.unitid= Drug.unitid) as unitname,
(select mfgname from Manufacture where Manufacture.mfgid= Drug.mfgid) as mfgname
from Drug
where drugid = coalesce('+case when @drugname is not null then cast(@drugname as varchar) else 'NULL'end+',drugid,'''')
AND
mfgid '+case when @mfgid > 0 then '=('+cast(@mfgid as varchar)+')' else ' is null or mfgid = mfgid' end+'
) A '

execute(@q)
select @q