Returning results from temporary tables in a sproc

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)

Returning results from temporary tables in a sproc

Author

Topic

neil
Starting Member

29 Posts

Posted - 2002-10-25 : 05:06:57

I have a stored procedure which creates a Temp table I want to return the results a asp script. The stored procedure works fine but does not return any results to the asp script I have included the sproc below

CREATE PROCEDURE up_HomePage_RandomImages
AS

DECLARE
@Counter int,
@RandID int,
@NumberOfImages int,
@Rowcount int

/*Set variables */
SET @Rowcount = 0
SET @Counter = 0
SET @NumberOfImages = 2

/* Create Temp Table */
CREATE TABLE #Temp (ID INT, ImageName varchar(50))

WHILE @Counter < @NumberOfImages
BEGIN
/* Generate random number */
SELECT @RandID = CONVERT(INT,RAND()*(MAX(ID) - MIN(ID)) + MIN(ID)) FROM GblRandomImages

/* Insert into temp table */
INSERT INTO #Temp
(ID, ImageName)
SELECT
ID, ImageName
FROM
GblRandomImages
WHERE
ID = @RandID AND
ID NOT IN (SELECT ID FROM #Temp)

/* Count the number of rows inserted */
SET @Rowcount = @Rowcount + @@ROWCOUNT

/* If Rowcount is equal to the number of Images required then break from loop */
IF @Rowcount = @NumberOfImages
BEGIN
BREAK
END

END

SELECT * FROM #Temp

DROP TABLE #Temp
GO

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-25 : 05:38:04

try putting
set nocount on
at the top of the sp.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

neil
Starting Member

29 Posts

Posted - 2002-10-25 : 05:49:07

Thanks SET NOCOUNT ON worked

jeroena
Yak Posting Veteran

76 Posts

Posted - 2002-10-25 : 07:10:09

Hi,

If you are using SQL Server 2000 you might consider using the 'table' datatype rather then a temporary table. It uses less resources / lockes as a temp table. You can use a variable of the table datatype in the same manner as a temp table (SELECT, INSERT, etc.). Look for 'table data type' in the BOL.

Jeroen.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources