· 8 years ago · May 16, 2018, 09:06 AM
1-- ================================================
2-- Template generated from Template Explorer using:
3-- Create Procedure (New Menu).SQL
4--
5-- Use the Specify Values for Template Parameters
6-- command (Ctrl-Shift-M) to fill in the parameter
7-- values below.
8--
9-- This block of comments will not be included in
10-- the definition of the procedure.
11-- ================================================
12IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[PV_IRReportRegistrationUserForDate]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
13DROP PROCEDURE [dbo].PV_IRReportRegistrationUserForDate
14GO
15PRINT 'Creating procedure PV_IRReportRegistrationUserForDate';
16GO
17CREATE PROCEDURE [dbo].PV_IRReportRegistrationUserForDate
18 -- NOTE: custom parameters here (see example below)--tu dodac filty
19 @StartDate DATETIME = '',
20 @EndDate DATETIME = '',
21 @Status TINYINT = 0,
22 @TimeZoneOffset INT,
23 @SortField VARCHAR(100) = '',
24 @SortDirection VARCHAR(5) = 'ASC',
25 @PageSize INT = 10,
26 @PageIndex BIGINT = 1,
27 @RecordCount BIGINT OUTPUT
28AS
29BEGIN
30 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- turn it on
31
32 DECLARE @Results TABLE
33 (
34 -- NOTE: custom columns here (see example below)
35 CreateDate DATETIME,
36 PhoneNumber NVARCHAR(50),
37 Email NVARCHAR(200),
38 Name NVARCHAR(200),
39 TextTask NVARCHAR(MAX),
40 StatusTask TINYINT,
41 RowId INT,
42
43 RowType TINYINT, -- 1-normal, 2-summary, 3-column_css_styles
44 CssStyle NVARCHAR(400)
45 )
46
47 -- normal rows
48 INSERT INTO @Results
49 SELECT -- NOTE: custom columns with css style here (see example below)
50 r.CreateDate
51 , r.PhoneNumber
52 , r.Email
53 , r.Name
54 , r.TextTask
55 , r.StatusTask
56 , 1 as RowId
57 , 1 as RowType
58 , '' as CssStyle -- NOTE: custom css style here (see example below)
59 FROM
60 (
61 SELECT t.CreateDateUTC as CreateDate
62 , u.PhoneNumber as PhoneNumber
63 , u.Email as Email
64 , u.FirstName + ' ' + u.LastName as Name
65 , t.Text as TextTask
66 , t.Status as StatusTask
67 FROM IRTask as t
68 LEFT JOIN IRUser as u ON t.IRFromUserID = u.ID
69 WHERE @StartDate < t.CreateDateUTC
70 AND @EndDate > t.CreateDateUTC
71 AND(@Status = 0 OR t.Status = @Status)
72 ) r
73
74 SELECT @RecordCount = COUNT(1)
75 FROM @Results
76
77 -- columns_css_style row
78 SELECT NULL as CreateDate, NULL as PhoneNumber, NULL as Email, NULL as Name, NULL as TextTask, NULL as StatusTask , NULL as RowId, 3 as RowType, s.Value as CssStyle
79 FROM (-- NOTE: style for custom columns or NULL here (see example below)
80 SELECT NULL as Value
81 UNION ALL
82 SELECT NULL as Value
83 UNION ALL
84 SELECT NULL as Value
85 UNION ALL
86 SELECT NULL as Value
87 UNION ALL
88 SELECT NULL as Value
89 UNION ALL
90 SELECT NULL as Value
91
92 UNION ALL
93 SELECT NULL as Value
94 UNION ALL
95 SELECT NULL as Value
96 UNION ALL
97 SELECT NULL as Value) s
98
99 UNION ALL
100
101 SELECT * FROM @Results
102
103 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- turn it off
104
105 IF @@ERROR<>0 GOTO ERROR_PROC;
106
107 --------------------------------------------------------------------------
108 -- exit point of the store proc
109 RETURN(0);
110
111 ERROR_PROC:
112 RETURN(99);
113END;
114GO
115-- end PV_IRReportRegistrationUserForDate