Arum's Pages

Sometimes when u have many filters in your searching data, then u need query in string format. It's avoid many 'if else' of field that u use to filter. I have a senior programmer who tell me how to write the sp. Here we go the example :

BEGINSET NOCOUNT ON;
declare @str NVarchar(4000) , @where varchar(2000), @UserId char(8)
select @UserId = 'U0000000', @where = '(m.DocumentKeywords like ''%og%'' or m.DocumentKeywords like ''%og%'') AND convert(datetime,m.DocumentCreatedDate,106) between ''01 Mar 2013'' and DATEADD(DAY, +1,''31 Mar 2013'')'
set @str=N'SELECT distinct m.DocumentId, m.DocumentName
FROM dbo.DocumentsAccess a inner join dbo.Documents m on a.DocumentId=m.DocumentId and m.stsrc <> ''D''
inner join Users u on m.DocumentCreatedById = u.UserId
left join Users um on m.DocumentModifiedById = um.UserId and u.stsrc <> ''D''
WHERE (a.Stsrc <> ''D'' and a.canAccess=''1'' and a.UserId = '''+ @UserId + ''') and '
set @str=@str+'('+@where +')'
+ 'ORDER BY m.DocumentName '

exec sp_executesql @str
print @str

END Then the result will be :

SELECT distinct m.DocumentId, m.DocumentName
FROM dbo.DocumentsAccess a inner join dbo.Documents m on a.DocumentId=m.DocumentId and m.stsrc <> 'D'
inner join Users u on m.DocumentCreatedById = u.UserId
left join Users um on m.DocumentModifiedById = um.UserId and u.stsrc <> 'D'
WHERE (a.Stsrc <> 'D' and a.canAccess='1' and a.UserId = 'U0000000') and ((m.DocumentKeywords like '%og%' or m.DocumentKeywords like '%og%') AND convert(datetime,m.DocumentCreatedDate,106) between '01 Mar 2013' and DATEADD(DAY, +1,'31 Mar 2013'))ORDER BY m.DocumentName