Stored Procedures or Query String? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Stored Procedures or Query String?

Author

Topic

Jeepaholic
Starting Member

36 Posts

Posted - 2002-06-26 : 14:06:28

Hi there. I found a couple really good articles and Stored Procedures for data paging on SQLTeam.com. My question has to do with whether or not it's OK to pass these SP's in a query string instead of creating an actual SP.

The reason I want to do this is because I need to dynamically create the tables, columns, etc...and I don't know how to do this in a SP. Each bit of code calling this "function" will require different tables, columns, etc.

I've included the code (and article links) below, is either of them better than the other? I'm new to SP's, so please go easy on me! <grin>

Thanks for any help!]
Al

.
.
VERSION 1
http://www.aspfaqs.com/webtech/062899-1.shtml


CREATE PROCEDURE sp_PagedItems
	(
	 @Page int,
	 @RecsPerPage int
	)
AS

-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON


--Create a temporary table
CREATE TABLE #TempItems
(
	ID int IDENTITY,
	Name varchar(50),
	Price currency
)


-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (Name, Price)
SELECT Name,Price FROM tblItem ORDER BY Price

-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)

-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
       MoreRecords = 
	(
	 SELECT COUNT(*) 
	 FROM #TempItems TI
	 WHERE TI.ID >= @LastRec
	) 
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec


-- Turn NOCOUNT back OFF
SET NOCOUNT OFF

.
.
.
VERSION 2
http://www.15seconds.com/Issue/010308.htm


CREATE PROCEDURE "sprocInformationTechnologyProjects"

@Page int,
@Size int

AS

DECLARE @Start int, @End int

BEGIN TRANSACTION GetDataSet

SET @Start = (((@Page - 1) * @Size) + 1)
IF @@ERROR <> 0
	GOTO ErrorHandler

SET @End = (@Start + @Size - 1)
IF @@ERROR <> 0
	GOTO ErrorHandler

CREATE TABLE #TemporaryTable
(
	Row int IDENTITY(1,1) PRIMARY KEY,
	Project varchar(100),
	Buyer int,
	Bidder int,
	AverageBid money
)
IF @@ERROR <> 0
	GOTO ErrorHandler

INSERT INTO #TemporaryTable
SELECT ...
// Any kind of select statement is possible with however many joins
//  as long as the data selected can fit into the temporary table.
IF @@ERROR <> 0
	GOTO ErrorHandler

SELECT Project, Buyer, Bidder, AverageBid
FROM #TemporaryTable
WHERE (Row >= @Start) AND (Row <= @End)
IF @@ERROR <> 0
	GOTO ErrorHandler

DROP TABLE #TemporaryTable

COMMIT TRANSACTION GetDataSet
RETURN 0

ErrorHandler:
ROLLBACK TRANSACTION GetDataSet
RETURN @@ERROR

Jeepaholic
Starting Member

36 Posts

Posted - 2002-06-26 : 15:54:34

One more question...If this query is put into a web application, and multiple users hit it at the same time, will the same temporary table be used - or will it create a separate one for each instance? It would obviously be a nightmare if they accessed the same temporary table. If this is a problem, how do I resolve it?

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-06-26 : 16:09:02

Not sure I understand what you are getting at for the first question but to put your mind at rest, local temporary tables are specific to the connection.

You can see this quite easily in Query Analyser.
Open 2 windows
In the first run


create table #win1(col1 int)

In the second run


select * from #win1

and you'll get an error


Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#win1'.

also run in window 1


use tempdb
go
sp_help #win1

you'll see the table name is padded with underscore characters



#win1_______________________________________________________________________________________________________________000000000016

HTH
Jasper Smith

Edited by - jasper_smith on 06/26/2002 16:09:53

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources