This SHOULD NOT WORK? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

This SHOULD NOT WORK?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-02-10 : 07:51:19

David Book writes "Based on all the reading I have done, this should not work. I did the reading trying to solve the "string" comma list when I could not get it to work.

Back End: SQL 7.0
Table Size (clients): Approx. 50,000 records
Table Size(s) Joined Tables: approx. 100 Records Each
Front End: Classic ASP
Objective. Allow user to select multiple filter criteria, Records per page, Page Number, and Sort/UpDown Criteria.
Problems.
1)SQL Wont allow "comma seperated list's" in query
2)Don't want to return a "HUGE" recordset to webserver, need paging

I got the paging (I'm new at this) from another tutorial. on sqlTeam/4Guys (sql team > 4 guys). Then modified it to accept multiple params. Many of which are Integers, converted to varChar, passed in as either "0", "ONE NUMBER", or a list of "numbers". This is the part that shouldn't work, but it seems to and I need to make sure I'm not missing something. I have tried it countless times and cannot get it to fail? So...

Here is the procedure....(again, all params are form field entries/selections in an ASP page

CREATE PROCEDURE sp_PagedClients
--created 02/07/2004 david book
--modified 02/07/2004 david book
--This procedure gets clients, creates a temp table, then return pages as needed. We do this to avoid sending GIANT result sets
--to the web server.
(
--Params

@NextPage int,
@RecsPerPage int,
@Sort varChar(75),
@UpDown varChar(50),
@DealerId varChar(50),
@StoreId varChar(1500),
@UserNumber varChar(1500),
@ClientStatus varChar(1500),
@ClientCategory varChar(1500),
@WebLead varChar(50), -- Y, N, or both
@DateStampOne varChar(75), -- all dates passed as 1/1/2000 12:00:00 PM format
@DateStampTwo varChar(75),
@DateModifiedOne varChar(75),
@DateModifiedTwo varChar(75),
@Search varChar(100),
@SearchWhat varChar(250),
@SearchType varChar(250)
--Search is item entered to search for, search what is the field, search type is the wildcard type

)

AS

SET NOCOUNT ON

--Create a temporary table
CREATE TABLE #TempItems
(
ID int IDENTITY,
ContactId int,
ClientLastName varchar(100),
ClientFirstName varchar(100),
Status int,
RepFirstName varchar(100),
RepLastName varchar(100),
WebLead varchar(20),
Make varchar(100),
Model varchar(100),
StoreName varchar(150),
DateEntered dateTime,
Source varchar(150),
Modified dateTime,
Category int
)

-- Insert the rows from upcoming select into the temp. table
-- declare sql string variable

DECLARE @mySQL varchar(3000)

SET @mySQL = 'INSERT INTO #TempItems (ContactId, ClientLastName, ClientFirstName, Status, RepFirstName, RepLastName, '
SET @mySQL = @mySQL + ' WebLead, Make, Model, StoreName, DateEntered, Source,Modified,Category ) '
SET @mySQL = @mySQL + ' SELECT tblUserContacts.UserContactId, tblUserContacts.LastName AS ClientLastName , tblUserContacts.FirstName AS ClientFirstName,'
SET @mySQL = @mySQL + ' tblUserContacts.Status, tblUsers.FirstName AS UserFirstName, tblUsers.LastName AS UserLastName , tblUserContacts.WebLead, '
SET @mySQL = @mySQL + ' tblUserContacts.Make, tblUserContacts.Model, Store.Name, tblUserContacts.DateStamp AS ClientDateStamp, SourceName.Name AS SourceName, '
SET @mySQL = @mySQL + ' tblUserContacts.Modified, tblUserContacts.Category '
SET @mySQL = @mySQL + ' FROM tblUserContacts '
SET @mySQL = @mySQL + ' INNER JOIN tblUsers ON tblUserContacts.UserNumber = tblUsers.UserNumber '
SET @mySQL = @mySQL + ' INNER JOIN tblStores AS Store ON tblUsers.Store = Store.StoreId '
SET @mySQL = @mySQL + ' LEFT JOIN tblUserSelect AS SourceName ON tblUserContacts.Source = SourceName.SelectId '
SET @mySQL = @mySQL + ' WHERE tblUserContacts.UserContactId > 0 '
SET @mySQL = @mySQL + ' AND tblUsers.DealerId = ' + convert(varChar, @DealerId)

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-10 : 09:31:25

You can jam almost anything in to varchar...

conversions are the issue...

DECLARE @x int, @y datetime, @z varchar, @v sql_variant
SELECT @x = 1, @y = Getdate(), @v = 123.456

SELECT @z = @x
SELECT @z

SELECT @z = @y
SELECT @z, CONVERT(varchar(25),@y)

SELECT @z = @v
SELECT @z

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources