troubleshooting Question

Dynamic SQL in a stored proc that uses Table variables for Paging?

Avatar of tmueller18
tmueller18 asked on
Microsoft SQL Server
7 Comments1 Solution220 ViewsLast Modified:
I have a proc that I use for a paging recordset. I use table varibles instead of temp tables. Everything works great except that I am unable to pass in a dynamic where clause.

below is my proc

CREATE PROCEDURE dbo.sp_Paging_Rowcount
    @PageNumber INT = 1,
    @RecordsPerPage INT = 50,
    @SortExpression int = 0,
    @SortAscending int,
    @TotalRecords int output

AS

BEGIN
      SET NOCOUNT ON
      
      DECLARE
      @ubound INT,
      @lbound INT,
      @pages INT,
      @rows INT

      SELECT @rows = COUNT(*),  @pages = COUNT(*) / @RecordsPerPage  FROM tblReferral R WITH (NOLOCK)
      
      IF @rows % @RecordsPerPage != 0 SET @pages = @pages + 1
      IF @PageNumber < 1 SET @PageNumber = 1
      IF @PageNumber > @pages SET @PageNumber = @pages
      
      SET @ubound = @RecordsPerPage * @PageNumber  
      SET @lbound = @ubound - (@RecordsPerPage - 1)  
      
       
      DECLARE @aname VARCHAR(64), @title VARCHAR(64)
      DECLARE @referral_id int, @referral_type varchar(50), @referral_name_first varchar(100), @referral_name_last varchar(100), @referral_family_ids varchar(2000)
      DECLARE @referral_date varchar(100), @referral_status varchar(10), @referral_address_number varchar(100), @referral_address_street varchar(100), @referral_address_state varchar(100), @referral_address_zip varchar(100), @city_description varchar(100)
      DECLARE @referral_family_id as int

      SET ROWCOUNT @lbound

IF @SortExpression = 5 --referral_type
      BEGIN
            If @SortAscending = 1
                  BEGIN
                        SELECT @referral_id = R.referral_id, @referral_type = R.referral_type, @referral_name_first = R.referral_name_first, @referral_name_last = R.referral_name_last, @referral_family_ids = '', @referral_family_id = r.referral_family_id,
                        @referral_date = R.referral_date, @referral_status = R.referral_status, @referral_address_number = R.referral_address_number, @referral_address_street = R.referral_address_street, @referral_address_state = R.referral_address_state, @referral_address_zip = R.referral_address_zip, @city_description = CC.city_description
                        FROM  tblReferral R INNER JOIN tblCity CC ON CC.city_id = R.referral_address_city_id
                        ORDER BY r.referral_type
                  
                        SET ROWCOUNT @RecordsPerPage
                        
                        -- return results
                        SELECT R.referral_id, R.referral_type, R.referral_name_first, R.referral_name_last, r.referral_family_id, R.referral_family_id,
                        R.referral_date, R.referral_status, R.referral_address_number, R.referral_address_street, R.referral_address_state, R.referral_address_zip, CC.city_description
                        FROM tblReferral R INNER JOIN tblCity CC ON CC.city_id = R.referral_address_city_id
                        WHERE R.referral_id   >= @referral_id
                        ORDER BY r.referral_type ASC
                  END
            ELSE
                  BEGIN
                        SELECT @referral_id = R.referral_id, @referral_type = R.referral_type, @referral_name_first = R.referral_name_first, @referral_name_last = R.referral_name_last, @referral_family_ids = '', @referral_family_id = r.referral_family_id,
                        @referral_date = R.referral_date, @referral_status = R.referral_status, @referral_address_number = R.referral_address_number, @referral_address_street = R.referral_address_street, @referral_address_state = R.referral_address_state, @referral_address_zip = R.referral_address_zip, @city_description = CC.city_description
                        FROM  tblReferral R INNER JOIN tblCity CC ON CC.city_id = R.referral_address_city_id
                        ORDER BY r.referral_type
                  
                        SET ROWCOUNT @RecordsPerPage
                        
                        -- return results
                        SELECT R.referral_id, R.referral_type, R.referral_name_first, R.referral_name_last, r.referral_family_id, R.referral_family_id,
                        R.referral_date, R.referral_status, R.referral_address_number, R.referral_address_street, R.referral_address_state, R.referral_address_zip, CC.city_description
                        FROM tblReferral R INNER JOIN tblCity CC ON CC.city_id = R.referral_address_city_id
                        WHERE R.referral_id   >= @referral_id
                        ORDER BY r.referral_type DESC
                  END
      END


if @SortExpression = 0
      BEGIN
            If @SortAscending = 1 -- ASC
                  BEGIN
                        SELECT @referral_id = R.referral_id, @referral_type = R.referral_type, @referral_name_first = R.referral_name_first, @referral_name_last = R.referral_name_last, @referral_family_ids = '', @referral_family_id = r.referral_family_id,
                        @referral_date = R.referral_date, @referral_status = R.referral_status, @referral_address_number = R.referral_address_number, @referral_address_street = R.referral_address_street, @referral_address_state = R.referral_address_state, @referral_address_zip = R.referral_address_zip, @city_description = CC.city_description
                        FROM  tblReferral R INNER JOIN tblCity CC ON CC.city_id = R.referral_address_city_id
                        ORDER BY r.referral_id
                  
                        SET ROWCOUNT @RecordsPerPage
                        
                        -- return results
                        SELECT R.referral_id, R.referral_type, R.referral_name_first, R.referral_name_last, r.referral_family_id, R.referral_family_id,
                        R.referral_date, R.referral_status, R.referral_address_number, R.referral_address_street, R.referral_address_state, R.referral_address_zip, CC.city_description
                        FROM tblReferral R INNER JOIN tblCity CC ON CC.city_id = R.referral_address_city_id
                        WHERE R.referral_id   >= @referral_id
                        ORDER BY r.referral_id ASC
                  END

            ELSE -- DESC
                  BEGIN
                        SELECT @referral_id = R.referral_id, @referral_type = R.referral_type, @referral_name_first = R.referral_name_first, @referral_name_last = R.referral_name_last, @referral_family_ids = '', @referral_family_id = r.referral_family_id,
                        @referral_date = R.referral_date, @referral_status = R.referral_status, @referral_address_number = R.referral_address_number, @referral_address_street = R.referral_address_street, @referral_address_state = R.referral_address_state, @referral_address_zip = R.referral_address_zip, @city_description = CC.city_description
                        FROM  tblReferral R INNER JOIN tblCity CC ON CC.city_id = R.referral_address_city_id
                        ORDER BY r.referral_id
                  
                        SET ROWCOUNT @RecordsPerPage
                        
                        -- return results
                        SELECT R.referral_id, R.referral_type, R.referral_name_first, R.referral_name_last, r.referral_family_id, R.referral_family_id,
                        R.referral_date, R.referral_status, R.referral_address_number, R.referral_address_street, R.referral_address_state, R.referral_address_zip, CC.city_description
                        FROM tblReferral R INNER JOIN tblCity CC ON CC.city_id = R.referral_address_city_id
                        WHERE R.referral_id   >= @referral_id
                        ORDER BY r.referral_id DESC
                  END
      END

      SELECT @TotalRecords = @rows

      SET ROWCOUNT 0

      
END
GO



Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 7 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros