@一个存储过程应用案例：
USE [CsWutong]
GO
/****** Object: StoredProcedure [dbo].[SearchBshi] Script Date: 03/27/2014 17:02:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[SearchBshi]
@fromProvince NVARCHAR(50) ,
@fromCity NVARCHAR(50) ,
@fromArea NVARCHAR(50) ,
@com_name NVARCHAR(50) ,
@service_item NVARCHAR(50) ,
@companyAddress NVARCHAR(200) ,
@intPageNo INT = 1 , --页号
@intPageSize INT = 15 --每页显示数
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Count NVARCHAR(50)
DECLARE @TmpSelect NVARCHAR(500)
----查询记录总数
SET @TmpSelect = 'select @Count = count(*) FROM (select ROW_NUMBER() OVER(PARTITION BY bshi_huiyuan_join.cust_id ORDER BY bshi_huiyuan_join.id) AS allcount,bshi_huiyuan_join.*,com.pic,com.com_name from (select bshi.* from bshi inner join huiyuan on bshi.cust_id=huiyuan.id where huiyuan.Verify>=3) as bshi_huiyuan_join inner join com on bshi_huiyuan_join.cust_id=com.cust_id) AS temp WHERE allcount=1'
IF ISNULL(@fromProvince, '') != ''
SET @TmpSelect = @TmpSelect + ' and sheng=''' + @fromProvince
+ ''''
IF ISNULL(@fromCity, '') != ''
SET @TmpSelect = @TmpSelect + ' and shi=''' + @fromCity + ''''
IF ISNULL(@fromArea, '') != ''
SET @TmpSelect = @TmpSelect + ' and service_area LIKE ''%'
+ @fromArea + '%'''
IF ISNULL(@companyAddress, '') != ''
SET @TmpSelect = @TmpSelect + ' AND dizhi LIKE ''%'
+ @companyAddress + '%'' '
IF ISNULL(@service_item, '') != ''
SET @TmpSelect = @TmpSelect + ' AND service_item LIKE ''%'
+ @service_item + '%'' '
IF ISNULL(@com_name, '') != ''
SET @TmpSelect = @TmpSelect + ' AND com_name LIKE ''%' + @com_name
+ '%'' '
EXECUTE sp_executesql @TmpSelect, --执行上面的sql语句
N'@Count int OUTPUT', --执行输出数据的sql语句，output出总记录数
@Count OUTPUT --这里返回@count的值，下边查询中，和结果集一起返回。
--查询详细数据
SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY O.paixu DESC, O.VipPx DESC, O.statepx DESC, O.Time DESC ) AS OrderId ,
O.*
FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY T.cust_id ORDER BY T.time DESC ) AS allcount ,
T.* ,
com.pic ,
com.com_name ,
com.jianjie ,
com.qq ,
COUNT = @Count
FROM ( SELECT *
FROM ( SELECT bshi.id ,
bshi.mingcheng ,
bshi.cust_id ,
domain ,
bshi.Sheng ,
bshi.Shi ,
bshi.Xian ,
bshi.Lianxiren ,
bshi.dizhi ,
bshi.service_area ,
bshi.service_item ,
bshi.time , --改过
CASE
WHEN huiyuan.vip = 1
AND ( DATEDIFF(dd,
bshi.[time],
GETDATE()) ) <= 20 --改过,去除了时间条件
THEN 1
ELSE 0 --SELECT TOP 10 * FROM bshi ORDER BY TIME desc SELECT DATEDIFF(dd,bshi.time,GETDATE()) AS testtime FROM bshi WHERE cust_id=495584
END AS VipPx ,
CASE
WHEN ( DATEDIFF(dd,
bshi.[time],
GETDATE()) ) <= 5
THEN huiyuan.Verify
ELSE 0
END AS statepx ,
CASE
WHEN bshi.state = 3
THEN bshi.paixu
ELSE 0
END AS paixu --改过
FROM bshi
INNER JOIN huiyuan ON bshi.cust_id = huiyuan.id
AND huiyuan.Verify >= 3
) AS b
) AS T
INNER JOIN com ON T.cust_id = com.cust_id
AND ( ISNULL(@com_name,
'') = '' --如果@com_name为空，就将值设置为空，如果不为空，就返回or后边语句。
OR com_name LIKE '%'
+ @com_name
+ '%'
)
AND ( ISNULL(@fromProvince,
'') = ''
OR sheng = @fromProvince
)
AND ( ISNULL(@fromCity,
'') = ''
OR shi = @fromCity
)
AND ( ISNULL(@fromArea,
'') = ''
OR T.service_area LIKE '%'
+ @fromArea
+ '%'
)
AND ( ISNULL(@companyAddress,
'') = ''
OR dizhi LIKE '%'
+ @companyAddress
+ '%'
)
AND ( ISNULL(@service_item,
'') = ''
OR service_item LIKE '%'
+ @service_item
+ '%'
)
) AS O
WHERE allcount = 1
) AS P
WHERE OrderId BETWEEN ( @intPageNo - 1 ) * @intPageSize + 1
AND @intPageNo * @intPageSize
END
管理
