asked on

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

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

StephenCairns

What are you using this for? if you were using ADO you could do it there.
What part are you trying to apply the where clause?

tmueller18

ASKER

ill build the query in the .net page and then i would like to pass it into the proc. It kicks back an error with the dynamic sql since there are table variables being used.

StephenCairns

Which part are you trying to execute dynamically ?
I dont see anything dynamic in the stored procedure

ASKER CERTIFIED SOLUTION

StephenCairns

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tmueller18

ASKER

the problem i run into are the table variables like this one.

SELECT @referral_id = R.referral_id

If its in a string it doesnt recognize it and throws an error. Make sense?

tmueller18

ASKER

i can do this in a proc using temp tables

exec ('SELECT R.referral_id, R.referral_name_first, R.referral_name_last, R.referral_family_id, R.referral_date, R.referral_type, R.referral_status FROM tblReferral R INNER JOIN tblCity CC ON CC.city_id = R.referral_address_city_id')

I would like to do this somehow in the the proc with table variables since its about 2x faster than the temp table proc.

tmueller18

ASKER

im using a temp table and was able to get the results that i wanted. thanks for the help