locked
Help to Dynamic Stored procedure RRS feed

  • Question

  • Hey all

    I'am currently trying to implement some dynamic parameters "@EventCategory, @Category and @City" to the RSSRows select statement below, but can't seem to get it to work. Is there anybody out there who can help?

    Thanks in advance

    PS: The @EventCategory, @Category and @City should only be  included in the where statement if the they are not = 0 . Something like dynamic SQL 

    IF @Categoryid > 0

    BEGIN

    WHERE RowNumber BETWEEN @FirstRow AND @LastRow AND CategoryId=@CategoryId ORDER By Id DESC;

    END

    Here is my Stored Procedure:

    CREATE PROCEDURE [dbo].[Get_searchCore_RssList]
    	(
    	@PageSize INT,
    	@Page INT,
    	@FirstRow INT,
    	@LastRow INT,
    	@StatusId INT,
    	@EventCategoryId int,
    	@CategoryId int,
    	@City varchar(50),
    	@SortBy varchar(50),
    	@SearchString varchar(200)
    	
    	)
    AS
    
    SET NOCOUNT ON
    
    SELECT	@FirstRow = ( @Page - 1) * @PageSize + 1,
    	@LastRow = (@Page - 1) * @PageSize + @PageSize;
    
    
    	WITH RSSRows  AS
    	(
    		SELECT	Id,CreatedDate,USERID,StatusId,Titel,[Description],postnr,City,[Image],Reward,CategoryName,
    			ROW_NUMBER() OVER (ORDER BY @SortBy ASC) AS RowNumber
    		FROM	dbo.Search_SmartPhone where StatusId = @StatusId and (Id Like '%' + @SearchString + '%' OR Titel Like '%' + @SearchString + '%' OR SubcategoryName Like '%' + @SearchString + '%' OR Description Like '%' + @SearchString + '%' or id in (select DISTINCT caseid from CaseDetails where value like '%' + @SearchString + '%'))
    	)
    
    
    SELECT	Id, RowNumber, CreatedDate,USERID,StatusId,Titel,[Description],postnr,City,[Image],Reward,CategoryName
    FROM	RSSRows
    WHERE	RowNumber BETWEEN @FirstRow AND @LastRow ORDER By Id DESC;
    
    RETURN

    Tuesday, February 14, 2012 4:23 PM

Answers

  • Your query looks like a "catch-all query".

    Please note the drawbacks of the currently proposed solution (risk of inefficient execution plan) as described in Gail Shaws blog (I can't describe it any better than that...).

    • Proposed as answer by Naomi N Tuesday, February 14, 2012 8:20 PM
    • Marked as answer by Kalman Toth Monday, February 20, 2012 11:36 AM
    Tuesday, February 14, 2012 7:46 PM

All replies

  • Try:

    WHERE RowNumber BETWEEN @FirstRow AND @LastRow

    AND (@EventCategoryId = 0 OR @EventCategoryId = EventCategoryId)

    AND (@CategoryId = 0 OR @CategoryId = CategoryId)

    AND (@City= '' OR @City= City)

    ORDER By Id DESC;


    Dave Frommer - BI Architect - Independent

    • Proposed as answer by Naomi N Tuesday, February 14, 2012 7:24 PM
    • Unproposed as answer by Naomi N Tuesday, February 14, 2012 8:20 PM
    Tuesday, February 14, 2012 4:31 PM
  • Hallo Dave

    Many many thanks for your reply. It works perfect.

    Best regards
    Henrik Stensgaard

    Henrik Stensgaard

    Tuesday, February 14, 2012 7:21 PM
  • Your query looks like a "catch-all query".

    Please note the drawbacks of the currently proposed solution (risk of inefficient execution plan) as described in Gail Shaws blog (I can't describe it any better than that...).

    • Proposed as answer by Naomi N Tuesday, February 14, 2012 8:20 PM
    • Marked as answer by Kalman Toth Monday, February 20, 2012 11:36 AM
    Tuesday, February 14, 2012 7:46 PM
  • Why do you have a DISTINCT with in an IN clause.

    id in (select DISTINCT caseid from CaseDetails where value like '%' + @SearchString + '%'))

    What good will selecting distinct values do here. If id's matches 1 record or thousands record, it's gonna be true else false.

    You are adding unnecessary burden to SQL Server.

    Even you could consider using EXISTS instead of IN


    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote helpful topics and Mark answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu

    Tuesday, February 14, 2012 8:14 PM
  • Try:

    WHERE RowNumber BETWEEN @FirstRowAND @LastRow

    AND (@EventCategoryId = 0 OR @EventCategoryId = EventCategoryId)

    AND (@CategoryId = 0 OR @CategoryId = CategoryId)

    AND (@City= '' OR @City= City)

    ORDER By Id DESC;

    But the conditions on the variables should be in the CTE, or else the paging will be spooky.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Tuesday, February 14, 2012 11:18 PM
    Tuesday, February 14, 2012 11:13 PM