Question Record Heavy browser paging

[lameness]

Hello all,

Im having a little trouble with making the navigation for a very record heavy browser based frontend for sql server 2000.

What i wish to do is to amend my current search so as to create a temporary table that extracts only 12 (or so) records from a potentially very large returned set of results. Only these 12 will then be displayed at any one time in the browser. However when i try to insert records into my new temporary table i get an error message and iam unsure how to solve the problem.

The error i get is:

"Error 0: An INSERT statement cannot contain a SELECT statement that assigns values to a variable."

And my stored procedure..(with amendments noted):

CREATE  PROCEDURE dbo.XBP2_Search_browser
@JobNumber  varchar(11)=NULL,
@DocumentID varchar(12)=NULL,
@StartDate  datetime=NULL,
@EndDate datetime=NULL,
@NSC varchar(10)=NULL,
@FormType char(1)=NULL,
@StartValue decimal(10,2)=NULL,
@EndValue decimal(10,2)=NULL,
@Country char(2)=NULL,
@SignatureNo integer=NULL,
@Status varchar(28)=NULL,
----new bit (record heavy paging)----------
@pageNumber int,
@pageSize int,
@totalRecords int output
-----------------new/>
AS

declare @Select as varchar(400)
--set dateformat dmy

-----new
declare @firstRecordInPage int
declare @lastRecordInPage int

--calculate the number of rows needed to get the current page size
select @firstRecordInPage = @pageNumber * @pageSize + 1
select @lastRecordInPage = @firstRecordInPage + @pageSize

--creates a temp table to copy the required data into
--add an additional ID column that is the primary key of this new table 
--also acts as an identity that will number the records copied into the table 

create table #singlePage
(
[ID] [int] IDENTITY (1, 1) not null,
[JobNumber] [varchar] (11) not null,
[DocumentID] [varchar] (12) not null,
[Entered] [datetime],
[Value] [numeric],
[NSC] [varchar] (10),
[StatusText] [varchar] (28),
[RejComments] [varchar] (100)
)
-----new/>

--ammended

insert into  #singlePage
(JobNumber, DocumentID, Entered, Value, NSC, StatusText, RejComments)
select @Select='SELECT JobNumber,  DocumentID, Entered, coalesce(cast(Value as numeric(10,2)), 0), NSC, StatusText, RejComments  FROM XBP2_JobReg WHERE'
if @JobNumber is not null select @Select=@Select + ' JobNumber like '''+@JobNumber+'%'' and '
if @DocumentID is not null select @Select=@Select + ' DocumentID like '''+@DocumentID+'%'' and '
if @StartDate is not null select @Select=@Select + ' datediff(dd,''' + convert(varchar(12),@StartDate,102) + ''',entered) >= 0 and '
if @EndDate is not null select @Select=@Select + ' datediff(dd,''' + convert(varchar(12),@EndDate,102) + ''',entered) <= 0 and '
if @NSC is not null select @Select=@Select + ' NSC like '''+@NSC+'%'' and '
if @FormType is not null select @Select=@Select + ' FormType='''+@FormType+''' and '
if @StartValue is not null select @Select=@Select + ' coalesce(Value, 0)>='+cast(@StartValue as varchar)+' and '
if @EndValue is not null select @Select=@Select + ' coalesce(Value, 0)<='+cast(@EndValue as varchar)+' and '
if @Country is not null select @Select=@Select + ' Country='''+@Country+''' and '
if @SignatureNo is not null select @Select=@Select + ' SignatureNo='''+@SignatureNo+''' and '
if @Status is not null select @Select=@Select + ' StatusText ='''+@Status+''' and '

select @Select=@select+' 1=1'+' order by jobnumber'
--select @select
exec (@Select)
GO

I realise that it doesn't want me want me to make statements such as

if @JobNumber is not null select @Select=@Select + ' JobNumber like '''+@JobNumber+'%'' and '

from the select statement....but i need to...they are the search functionality.
Im pretty new to this so can anyone please let me know the best way to work around this issue?

Just to recap i select records from a very large table and then i wanted to make a new table and insert a small number these selected records into the new table...thats what it doesnt like. Thanks for taking the time to read this...i hope it makes sense.

 

[cjard]

Er...

Yuck!

You need to rewrite your query to be more wildcard compatible..

Consider this:

INSERT INTO tblDest
SELECT * FROM tblSrc
WHERE
  (col1 = @col1 OR @col1 is null) AND
  (col2 = @col2 OR @col2 is null)

Now, if you supply a value for @col1 and leave col2 null then only rows with a matching col1 are returned. If you supply both values.. etc

I'm sure you can work out what to do now. This statement is a lot more compatible with what youre trying to do

Note that if youre doing wildcard searches:

WHERE
  (col1 LIKE @col1 OR @col1 is null) AND
  (col2 LIKE @col2 OR @col2 is null)

and you pass the % wilcard INSIDE THE PARAMETER.. so set you @col1 parameter value to be a string of:

Smith%

and you will get all records starting with smith.

You can of course concat it on in the query, but this makes it less flexible because it will always be wildcarded whereas the other way you can give users the option of wildcarding or not