hgh.just.hony
سه شنبه 06 تیر 1391, 14:02 عصر
سلام دوستای خوب
یه سوال فوری داشتم؟؟؟
من چندین ورودی دارم که ممکن اکثرشون null باشند
حالا چجوری سرچ کنم
این کدمه اما مثلا وقتی فقط نام کالا را وارد می کنم بهم کالاهایی با نام های دیگه هم نشون میده:متفکر:
.................................................. .................................................. ..........................................
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[usp_Advance_search]
-- Add the parameters for the stored procedure here
@type nvarchar(50)= null ,
@exact_word_name nvarchar(300)= null,
@like_word_name_detail nvarchar(300)= null,
@not_like_word nvarchar(300)= null,
@Down_price money= null ,
@top_price money= null,
@Detail nvarchar(1024)= null,
@City_provinc nvarchar(100)= null,
@type_cmp_prd nvarchar(500)= null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if @type = 'کالا'
begin
SELECT distinct(dbo._Product.Prd_Name),
dbo._Product.Prd_Id ,dbo._Product.PrdTyp_CountUnit,
dbo.[_Company&Users].Cmp_Id ,dbo._Product. Prd_Price,
dbo.[_Company&Users].Usr_Id,dbo._ProductType.PrdTyp_Title,
'~/main_pages/profile/'+ dbo._Product.Prd_Image as Prd_Image,
dbo.ReplaceCRWithBR(dbo._Product.Prd_Comment) as prd,
--substring(dbo._Product.Prd_Comment,1,200) as prd,
dbo._Company.Cmp_Name,
dbo._Company.Cmp_webSite,
dbo._Company.Cmp_Email,
dbo._Users.Usr_UserName,dbo._Users.Usr_Email ,
dbo._Province.Prv_Name,dbo.ReplaceCRWithBR(dbo._Co mpany.Cmp_Detail) as cmp
FROM dbo._City INNER JOIN
dbo._Company ON dbo._City.Cty_Id = dbo._Company.City_Id INNER JOIN
dbo.[_Company&Users] ON dbo._Company.Cmp_Id = dbo.[_Company&Users].Cmp_Id INNER JOIN
dbo._CompanyType ON dbo._Company.CmTy_Id = dbo._CompanyType.CmTy_Id INNER JOIN
dbo._Product ON dbo._Company.Cmp_Id = dbo._Product.Cmp_Id INNER JOIN
dbo.[_Product&ProductType] ON dbo._Product.Prd_Id = dbo.[_Product&ProductType].Prd_Id INNER JOIN
dbo._ProductType ON dbo.[_Product&ProductType].PrdTyp_Id = dbo._ProductType.PrdTyp_Id INNER JOIN
dbo._Province ON dbo._City.Prv_Id = dbo._Province.Prv_Id INNER JOIN
dbo._Users ON dbo.[_Company&Users].Usr_Id = dbo._Users.Usr_Id
where dbo._Product.Ped_IsDeleted = 0 and dbo._Company.Cmp_IsDeleted=0
and dbo.[_Company&Users].CmpUsr_IsActive=1 and dbo._Users.Usr_IsDeleted=0 and
(@exact_word_name IS NULL OR dbo._Product.Prd_Name =@exact_word_name ) AND
(@like_word_name_detail IS NULL OR dbo._Product.Prd_Name =@like_word_name_detail ) OR
(@like_word_name_detail IS NULL OR dbo._Product.Prd_Comment =@like_word_name_detail ) AND
((@not_like_word IS NULL) OR (dbo._Product.Prd_Name != @like_word_name_detail )) AND
((@Down_price IS NULL)and (@top_price IS NULL) OR (dbo._Product.Prd_Price between @Down_price and @top_price )) AND
((@Detail IS NULL) OR (dbo._Product.Prd_Comment =@Detail )) AND
((@City_provinc IS NULL) OR ( dbo._Province.Prv_Name=@City_provinc or dbo._City.Cty_Name = @City_provinc )) AND
((@type_cmp_prd IS NULL) OR (dbo._Product.Prd_Comment =@type_cmp_prd ))
end
END
.................................................. .................................................. ..........................................
اما من میخوام هرچی کاربر بیشتر داده به استورپروسیجرم پاس داد منم سرچم دقیقتر بشه
میشه کمکم کنین؟؟؟؟؟:قلب:
یه سوال فوری داشتم؟؟؟
من چندین ورودی دارم که ممکن اکثرشون null باشند
حالا چجوری سرچ کنم
این کدمه اما مثلا وقتی فقط نام کالا را وارد می کنم بهم کالاهایی با نام های دیگه هم نشون میده:متفکر:
.................................................. .................................................. ..........................................
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[usp_Advance_search]
-- Add the parameters for the stored procedure here
@type nvarchar(50)= null ,
@exact_word_name nvarchar(300)= null,
@like_word_name_detail nvarchar(300)= null,
@not_like_word nvarchar(300)= null,
@Down_price money= null ,
@top_price money= null,
@Detail nvarchar(1024)= null,
@City_provinc nvarchar(100)= null,
@type_cmp_prd nvarchar(500)= null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if @type = 'کالا'
begin
SELECT distinct(dbo._Product.Prd_Name),
dbo._Product.Prd_Id ,dbo._Product.PrdTyp_CountUnit,
dbo.[_Company&Users].Cmp_Id ,dbo._Product. Prd_Price,
dbo.[_Company&Users].Usr_Id,dbo._ProductType.PrdTyp_Title,
'~/main_pages/profile/'+ dbo._Product.Prd_Image as Prd_Image,
dbo.ReplaceCRWithBR(dbo._Product.Prd_Comment) as prd,
--substring(dbo._Product.Prd_Comment,1,200) as prd,
dbo._Company.Cmp_Name,
dbo._Company.Cmp_webSite,
dbo._Company.Cmp_Email,
dbo._Users.Usr_UserName,dbo._Users.Usr_Email ,
dbo._Province.Prv_Name,dbo.ReplaceCRWithBR(dbo._Co mpany.Cmp_Detail) as cmp
FROM dbo._City INNER JOIN
dbo._Company ON dbo._City.Cty_Id = dbo._Company.City_Id INNER JOIN
dbo.[_Company&Users] ON dbo._Company.Cmp_Id = dbo.[_Company&Users].Cmp_Id INNER JOIN
dbo._CompanyType ON dbo._Company.CmTy_Id = dbo._CompanyType.CmTy_Id INNER JOIN
dbo._Product ON dbo._Company.Cmp_Id = dbo._Product.Cmp_Id INNER JOIN
dbo.[_Product&ProductType] ON dbo._Product.Prd_Id = dbo.[_Product&ProductType].Prd_Id INNER JOIN
dbo._ProductType ON dbo.[_Product&ProductType].PrdTyp_Id = dbo._ProductType.PrdTyp_Id INNER JOIN
dbo._Province ON dbo._City.Prv_Id = dbo._Province.Prv_Id INNER JOIN
dbo._Users ON dbo.[_Company&Users].Usr_Id = dbo._Users.Usr_Id
where dbo._Product.Ped_IsDeleted = 0 and dbo._Company.Cmp_IsDeleted=0
and dbo.[_Company&Users].CmpUsr_IsActive=1 and dbo._Users.Usr_IsDeleted=0 and
(@exact_word_name IS NULL OR dbo._Product.Prd_Name =@exact_word_name ) AND
(@like_word_name_detail IS NULL OR dbo._Product.Prd_Name =@like_word_name_detail ) OR
(@like_word_name_detail IS NULL OR dbo._Product.Prd_Comment =@like_word_name_detail ) AND
((@not_like_word IS NULL) OR (dbo._Product.Prd_Name != @like_word_name_detail )) AND
((@Down_price IS NULL)and (@top_price IS NULL) OR (dbo._Product.Prd_Price between @Down_price and @top_price )) AND
((@Detail IS NULL) OR (dbo._Product.Prd_Comment =@Detail )) AND
((@City_provinc IS NULL) OR ( dbo._Province.Prv_Name=@City_provinc or dbo._City.Cty_Name = @City_provinc )) AND
((@type_cmp_prd IS NULL) OR (dbo._Product.Prd_Comment =@type_cmp_prd ))
end
END
.................................................. .................................................. ..........................................
اما من میخوام هرچی کاربر بیشتر داده به استورپروسیجرم پاس داد منم سرچم دقیقتر بشه
میشه کمکم کنین؟؟؟؟؟:قلب: