Mature SQL paging storage process (UP_General_SelectByWhere)

View Code

1 GO
2/***** Object: StoredProcedure [dbo]. [UP_General_SelectByWhere] Script Date: 03/17/2011 10:49:23 ******/
3 SET ANSI_NULLS ON
4 GO
5 SET QUOTED_IDENTIFIER ON
6 GO
7 -- ============================================== ========
8 -- Author: <Author, Name>
9 -- Create date: <Create Date,>
10 -- Description: <Description,>
11 -- ============================================== ========
12 alter procedure [dbo]. [UP_General_SelectByWhere]
13 (
14 @ strSelect NVarChar (1000 ),
15 @ strWhere NVarChar (4000 ),
16 @ strTable NVarChar (50 ),
17 @ strorder1_nvarchar (100 ),
18 @ OrderType bit = 0
19)
20
21 BEGIN
22 DECLARE @ strSQL varchar (8000) -- subject sentence
23 DECLARE @ strOrder varchar (400) -- sort type
24 IF (@ strOrderfld! = '')
25 BEGIN
26 if @ OrderType! = 0 -- descending order (desc)
27 BEGIN
28 set @ strOrder = 'ORDER BY' + @ strOrderfld + 'desc' -- If @ OrderType is not 0, execute the descending order. This sentence is very important!
29 END
30 ELSE -- Ascending (asc)
31 BEGIN
32 set @ strOrder = 'ORDER BY' + @ strOrderfld + 'asc'
33 END
34 SET @ strSQL = 'select' + @ strSelect + 'from' + @ strTable + 'where 1 = 1' + @ strWhere + @ strOrder
35 END
36 ELSE
37 BEGIN
38 SET @ strSQL = 'select' + @ strSelect + 'from' + @ strTable + 'where 1 = 1' + @ strWhere
39 END
40 EXECUTE (@ strSQL)
41 END