Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ultimate_senthil
Starting Member
13 Posts |
 Posted - 2009-01-16 : 14:10:28
|
| hi,Based on the below type inputs i have to search in the table fileds,procedure have four parameters @metadata parameter receive the below inputs.example @metadata = country sports club 1. country sports club 2 country "sports" club3 country "sports" "club" if the input parameter get the 1 type input the query should search based on country, sports, club. the result should have all the three words. if the input parameter get the 2 type input, it has double quotes so "sports" word alone search. the result should have only sports.if the input parameter get the 3 type input, it has two double quotes word ,so "sports" as well as "club" words alone search.the result should have only sports and club.below is my procedureALTER PROCEDURE [dbo].[DAMS_SP_SearchforPictures] ( @UserId AS BIGINT, @Metadata AS VARCHAR(1000), @PageSize AS INT, @PageNo AS INT ) AS BEGIN SET NOCOUNT ON; DECLARE @StartIndex AS INT DECLARE @EndIndex AS INT SET @StartIndex = (@PageSize * (@PageNo - 1)) + 1 SET @EndIndex = @PageSize * @PageNo DECLARE @SelQry AS VARCHAR(2000) SET @SelQry = 'SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY P.PictureId ASC) AS RowNum, C.CategoryName, S.SubCategory, PU.PublisherName, M.MagazineName, P.PictureId as PicId ,P.Title, P.PictureLoRes, P.PictureWMLoRes, P.UniqueId, P.Description, P.Photographer, P.SpecialRestrictions, P.Keywords,CASE WHEN S1.PictureID IS NOT NULL THEN ''V'' ELSE ''N''END As PID,S1.ImageSetId AS ImgSID,S2.ImageSetName FROM DAMS_Tbl_Pictures P LEFT OUTER JOIN DAMS_Tbl_ImageCollection S1 ON P.PictureId = S1.PictureIDLEFT OUTER JOIN DAMS_Tbl_ImageSet S2 ON S1.ImageSetId = S2.ImageSetId JOIN DAMS_Tbl_Category C ON P.CategoryId = C.CategoryId JOIN DAMS_Tbl_SubCategory S ON P.SubCategoryId = S.SubCategoryId JOIN DAMS_Tbl_PublisherDetails PU ON P.PublisherId = PU.PublisherId JOIN DAMS_Tbl_Magazine M ON P.MagazineId = M.MagazineId AND (C.CategoryName LIKE ''%' + @Metadata + '%'' OR S.SubCategory LIKE ''%' + @Metadata + '%'' OR PU.PublisherName LIKE ''%' + @Metadata + '%'' OR M.MagazineName LIKE ''%' + @Metadata + '%'' OR P.Title LIKE ''%' + @Metadata + '%'' OR P.Description LIKE ''%' + @Metadata + '%'' OR P.Photographer LIKE ''%' + @Metadata + '%'' OR P.Keywords LIKE ''%' + @Metadata + '%'' OR P.SpecialRestrictions LIKE ''%' + @Metadata + '%'')) AS Pictures 'IF ( @UserId <> 0 )BEGIN SET @SelQry = @SelQry + 'Where Pictures.PicId not in(SELECT RP.PictureId FROM DAMS_Tbl_PictureRestrictTerr RP LEFT OUTER JOIN DAMS_Tbl_RegisteredUsers RU ON RP.Territoryid = RU.CountryId WHERE RU.UserId = '+ CAST(@UserId AS VARCHAR(10)) + ') AND (RowNum BETWEEN ' + CAST(@StartIndex AS VARCHAR(10)) + ' AND ' + CAST(@EndIndex AS VARCHAR(10)) + ')' ENDELSEBEGIN SET @SelQry = @SelQry + 'WHERE (RowNum BETWEEN ' + CAST(@StartIndex AS VARCHAR(10)) + ' AND ' + CAST(@EndIndex AS VARCHAR(10)) + ')' END -- PRINT @SelQry EXEC(@SelQry) END senthilkumar |
|
|
|
|
|
|
|
searching