CREATE PROC Proc_Search_Document
@keyword NVARCHAR(500),
@branchID INT,
@levelID INT,
@listTagID NVARCHAR(500),
@pageNum INT,
@pageSize INT
AS
BEGIN

--SELECT * FROM dbo.Tag WHERE Name = 'c#'
DECLARE @SQL NVARCHAR(max)
--SELECT DISTINCT d.Title, d.ShortDescription, D.TimeActive, U.RealName, U.Avatar, U.ReputationPoint, D.LastActiveType, D.TagName, L.Name, D.TotalVote, D.TotalView, D.TotalDiscussion FROM dbo.Doc_Document AS D JOIN dbo.Level AS L ON D.LevelId=L.Id JOIN dbo.InformationUser AS U ON D.UserIdLastActive = U.Id 
--ORDER BY D.TimeActive DESC OFFSET  (@pageNum - 1) * @pageSize ROWS FETCH NEXT @pageSize ROWS only  
-- JOIN dbo.Doc_TagInDocument AS T ON D.Id = t.DocumentId WHERE t.TagId IN (@listTagID)
--SELECT D.Published, D.Deleted, D.ImproveId, D.UserId,D.Draft, D.UserIdLastActive, D.Id FROM dbo.Doc_Document AS D, dbo.InformationUser AS U

SET @SQL = 'SELECT DISTINCT 
d.Title, 
d.ShortDescription, 
d.Image, 
D.TimeActive, 
U.RealName, 
U.Avatar, 
U.ReputationPoint, 
D.LastActiveType, 
D.TagName, 
L.Name as LevelName, 
D.TotalVote, 
D.TotalView, 
D.TotalDiscussion, 
D.Published, 
D.Deleted, 
D.ImproveId, 
D.UserId,
D.Draft,
D.UserIdLastActive,
D.Id
FROM dbo.Doc_Document AS D 
JOIN dbo.Level AS L ON D.LevelId=L.Id 
JOIN dbo.InformationUser AS U ON D.UserIdLastActive = U.Id '

DECLARE @IsHavaParam BIT = 0

IF (@listTagID IS not NULL AND @listTagID <> '')
	BEGIN		
		SET @IsHavaParam = 1       

        SET @SQL = @SQL + ' JOIN dbo.Doc_TagInDocument AS T ON D.Id = t.DocumentId WHERE t.tagID IN (' + @listTagID + ') '        
    END


IF (@keyword IS NOT NULL and @keyword <>'')
	BEGIN
		IF (@IsHavaParam = 0)
		BEGIN
			SET @SQL = @SQL + ' where '
			SET @IsHavaParam = 1
        END
		ELSE
		BEGIN
		SET @SQL = @SQL + ' and '
        END

        SET @SQL = @SQL + ' Title LIKE N''%' + @keyword + '%'''        
    END

IF (@branchID IS not NULL AND @branchID <> -1)
	BEGIN
		IF (@IsHavaParam = 0)
		BEGIN
			SET @SQL = @SQL + ' where '
			SET @IsHavaParam = 1
        END
		ELSE
		BEGIN
		SET @SQL = @SQL + ' and '
        END

        SET @SQL = @SQL + ' BranchId = ' + CAST(@branchID AS NVARCHAR(10))   
    END    

IF (@levelID  IS NOT NULL AND @levelID <> -1)
	BEGIN
		IF (@IsHavaParam = 0)
		BEGIN
			SET @SQL = @SQL + ' where '
			SET @IsHavaParam = 1
        END
		ELSE
		BEGIN
		SET @SQL = @SQL + ' and '
        END

        SET @SQL = @SQL + 'LevelId = ' + CAST(@levelID AS NVARCHAR(10))   
    END 	

SET @SQL = @SQL + ' ORDER BY D.TimeActive DESC OFFSET ' + cast((@pageNum - 1) * @pageSize AS nvarchar(10)) + ' ROWS FETCH NEXT '+ CAST(@pageSize AS NVARCHAR(10)) +' ROWS ONLY '

PRINT @SQL

EXEC sp_executesql @SQL

END
GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105