ندااحمدی
چهارشنبه 25 آبان 1390, 14:14 عصر
چطورمیتوان search ای نوشت که با ویرگول هم جستجوی متفاوتی داشته باشه
USE [BidarDb]
GO
/****** Object: StoredProcedure [dbo].[phy_Request_member_selectSearch] Script Date: 11/16/2011 14:43:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[phy_Request_member_selectSearch]
(
@Profile_id int = -1,
@members_id int = -1,
@phy_test_id int = -1,
@phy_request_id int = -1,
@SearchKey NVarchar(50)
)
AS
Begin
Select
(select top 1 pl.phy_request_log_date from tbl_phy_request_log pl where pl.phy_request_id= pr.phy_request_id ) AS phy_request_log_date,
pr.phy_request_id,
m.members_id,
m.members_name,
pr.phy_request_title,
pr.phy_request_level,
pr.phy_request_description,
pr.phy_request_replytext,
pr.phy_request_replyfulltext,
pt.phy_test_id,
pt.phy_test_title,
pt.phy_test_code,
p.Profile_Id,
p.Profile_Title_Fa,
p.Profile_JobTitle_Fa,
prt.phy_request_test_status,
pt.phy_test_price,
( Case When pr.phy_request_level <> 3 Then 1 Else Case When @phy_test_id = -1 Then 1 Else Case when ma.IsActive = 1 Then 1 Else 0 End End End) as isAccess,
prt.phy_request_test_id
from tbl_Members m
Inner Join
tbl_phy_request pr
On pr.members_id = m.members_id
Left Join
tbl_phy_request_test prt
On prt.phy_request_id = pr.phy_request_id
Left Join
tbl_phy_test pt
On pt.phy_test_id = prt.phy_test_id
Left Join
tbl_Profiles p
On p.Profile_Id = pr.Profile_Id
Left Join
tbl_Members_Access ma
On ma.members_id = m.members_id
And ma.Module_Id = 40 And ma.Record_Id = prt.phy_request_test_id
Where
(@Profile_id = -1 Or p.Profile_Id = @Profile_id)
And
(@members_id = -1 Or m.members_id = @members_id)
And
(@phy_test_id = -1 Or pt.phy_test_id = @phy_test_id)
And
(@phy_request_id = -1 Or pr.phy_request_id = @phy_request_id)
And
(
m.members_name like N'%' + @SearchKey + '%' Or
pr.phy_request_title like N'%' + @SearchKey + '%' Or
pr.phy_request_title like N'%' + @SearchKey + '%' Or
pr.phy_request_level like N'%' + @SearchKey + '%' Or
pr.phy_request_description like N'%' + @SearchKey + '%' Or
pr.phy_request_replytext like N'%' + @SearchKey + '%' Or
pr.phy_request_replyfulltext like N'%' + @SearchKey + '%' Or
pt.phy_test_title like N'%' + @SearchKey + '%' Or
pt.phy_test_code like N'%' + @SearchKey + '%' Or
p.Profile_Title_Fa like N'%' + @SearchKey + '%' Or
p.Profile_JobTitle_Fa like N'%' + @SearchKey + '%' Or
prt.phy_request_test_status like N'%' + @SearchKey + '%' Or
pt.phy_test_price like N'%' + @SearchKey + '%'
)
order by
phy_request_log_date Desc
End
USE [BidarDb]
GO
/****** Object: StoredProcedure [dbo].[phy_Request_member_selectSearch] Script Date: 11/16/2011 14:43:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[phy_Request_member_selectSearch]
(
@Profile_id int = -1,
@members_id int = -1,
@phy_test_id int = -1,
@phy_request_id int = -1,
@SearchKey NVarchar(50)
)
AS
Begin
Select
(select top 1 pl.phy_request_log_date from tbl_phy_request_log pl where pl.phy_request_id= pr.phy_request_id ) AS phy_request_log_date,
pr.phy_request_id,
m.members_id,
m.members_name,
pr.phy_request_title,
pr.phy_request_level,
pr.phy_request_description,
pr.phy_request_replytext,
pr.phy_request_replyfulltext,
pt.phy_test_id,
pt.phy_test_title,
pt.phy_test_code,
p.Profile_Id,
p.Profile_Title_Fa,
p.Profile_JobTitle_Fa,
prt.phy_request_test_status,
pt.phy_test_price,
( Case When pr.phy_request_level <> 3 Then 1 Else Case When @phy_test_id = -1 Then 1 Else Case when ma.IsActive = 1 Then 1 Else 0 End End End) as isAccess,
prt.phy_request_test_id
from tbl_Members m
Inner Join
tbl_phy_request pr
On pr.members_id = m.members_id
Left Join
tbl_phy_request_test prt
On prt.phy_request_id = pr.phy_request_id
Left Join
tbl_phy_test pt
On pt.phy_test_id = prt.phy_test_id
Left Join
tbl_Profiles p
On p.Profile_Id = pr.Profile_Id
Left Join
tbl_Members_Access ma
On ma.members_id = m.members_id
And ma.Module_Id = 40 And ma.Record_Id = prt.phy_request_test_id
Where
(@Profile_id = -1 Or p.Profile_Id = @Profile_id)
And
(@members_id = -1 Or m.members_id = @members_id)
And
(@phy_test_id = -1 Or pt.phy_test_id = @phy_test_id)
And
(@phy_request_id = -1 Or pr.phy_request_id = @phy_request_id)
And
(
m.members_name like N'%' + @SearchKey + '%' Or
pr.phy_request_title like N'%' + @SearchKey + '%' Or
pr.phy_request_title like N'%' + @SearchKey + '%' Or
pr.phy_request_level like N'%' + @SearchKey + '%' Or
pr.phy_request_description like N'%' + @SearchKey + '%' Or
pr.phy_request_replytext like N'%' + @SearchKey + '%' Or
pr.phy_request_replyfulltext like N'%' + @SearchKey + '%' Or
pt.phy_test_title like N'%' + @SearchKey + '%' Or
pt.phy_test_code like N'%' + @SearchKey + '%' Or
p.Profile_Title_Fa like N'%' + @SearchKey + '%' Or
p.Profile_JobTitle_Fa like N'%' + @SearchKey + '%' Or
prt.phy_request_test_status like N'%' + @SearchKey + '%' Or
pt.phy_test_price like N'%' + @SearchKey + '%'
)
order by
phy_request_log_date Desc
End