Thread: Spliced tables, paged results

Here is a nifty little SP I came up with the other day so I thought I would share.

The sp pulls data from two similar tables into a temporary table and then grabs a page of results from this table to send back to your page.

I have it using a dynamic statement to pull data from an old table which was a little poorly thought out when created and combining it with a list from one of my tables.

Hope someone finds it handy, it sure made my job easier!

Code:

USE [table]
GO
/****** Object:  StoredProcedure [dbo].[combine_list]    Script Date: 09/16/2008 16:06:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Derek Robertson
-- Create date: 10 Sept 2008
-- Description:	Splice two tables for WP listings
-- =============================================
CREATE PROCEDURE [dbo].[combine_list]
	(
	 @intState int
	,@strState varchar(255)
	,@intCat int
	,@intPage int
	,@intRecsPerPage int
	)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @FirstRec int
	   ,@LastRec int
	   ,@sql nvarchar(4000)
	SELECT @FirstRec = (@intPage - 1) * @intRecsPerPage
	SELECT @LastRec = (@intPage * @intRecsPerPage + 1)

	-- Create a temp table to combine both tables we are searching
	CREATE TABLE #Combined_List(
	TempID int IDENTITY,
	ID int,
	Name varchar(255),
	City varchar(255),
	State varchar(255),
	Phone varchar(75),
	Website varchar(255),
	Website2 varchar(255),
	Blurb text,
	ImageSm varchar(75),
	adType bit
	)

    -- Insert statements for procedure here (dynamic)
	SELECT @sql = 
	'INSERT INTO #Combined_List (
	ID, 
	Name,
	City,
	State,
	Phone,
	Website,
	Website2,
	Blurb,
	ImageSm,
	adType
	)
	SELECT dir.ID
		 , dir.Name
		 , dir.City
		 , dir.State
		 , dir.Phone
		 , dir.Website
		 , dir.Website2
		 , dir.Blurb
		 , dir.ImageSm
		 , ''0''
	FROM dbo.tbl_categories AS cat 
	INNER JOIN dbo.tbl_joiner AS wp ON cat.categoryID = wp.wp_cat 
	INNER JOIN dbo.tbl_list AS dir ON wp.dir_id = dir.ID 
	WHERE (dir.'+quotename(@strState, '')+' = 1) AND (cat.categoryID = '+str(@intCat)+') Order By Name'

	EXEC(@sql)

	INSERT INTO #Combined_List (
	ID, 
	Name,
	City,
	State,
	Phone,
	Website,
	Website2,
	Blurb,
	ImageSm,
	adType
	)
	SELECT listing_id
		 , Name
		 , City
		 , State
		 , NULL
		 , Website
		 , NULL
		 , Blurb
		 , NULL
		 , '1'
	FROM dbo.dir_listings WHERE cat_id = @intCat And state_id = @intState Order By Name

	SELECT *,
		   TotalRecords =
		(SELECT COUNT(*) FROM #Combined_List)
	FROM #Combined_List
	WHERE TempID > @FirstRec AND TempID < @LastRec
END