To display the banners in sequential manner

jijish · January 3rd, 2009, 05:59 AM

I want to display the banners in a sequential manner instead of displaying it randomly.Say for eg if there are 3 records then it should display 1st then 2nd and then 3rd. Following is the stored procedure which displays the banner randomly.But I want it in sequential manner.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[sp_RandomBanner]
as
Declare @rows int
Declare @nRandNum int
create table #TempTable
(
dtPostDate datetime,
vchrLinkURL varchar(100),
vchrImagePath varchar(100),
vchrImageName varchar(50),
vchrStatus varchar(50),
vchrChangeStatus varchar(50),
intBannerID int identity(1,1)
)
INSERT INTO #TempTable
Select dtPostDate, vchrLinkURL, vchrImagePath,vchrImageName,vchrStatus,vchrChangeS tatus From tblBanner where vchrStatus='Running'
select @rows=count(*) from #TempTable
select @nRandNum=Round(((@rows - 1) * Rand() + 1), 0)
select * from #TempTable where intBannerID=@nRandNum and vchrStatus='Running'

Regards,
Jijish

jminatel · January 3rd, 2009, 09:05 AM

Please don't cross post:
To display the banners in sequential manner

gbianchi · January 4th, 2009, 03:20 PM

Hello. You want to display the banners sequential for the same user of for diferents users?? why just not add a table that takes the count??

Old Pedant · January 4th, 2009, 05:42 PM

I don't understand the point of creating the temp table, AT ALL.

You don't say how you are *using* this SQL, but if it is part of ANY kind of conventional web site/web page (ASP, ASP.NET, PHP, JSP), that temp table will be WIPED OUT each and every time the web page is sent to the browser, because ALL these technologies only keep the connection open so long as the page is being rendered and then close it once the HTML is sent to the browser. And closing the connect DELETES the temp table!

Tell us what technology you are using for your web page: ASP, ASP.NET, PHP, JSP, CF, or what???

Incidentally, your code for picking one random banner is HORRIBLY over-complicated for SQL Server.

You could accomplish the same thing with no need of the useless temp table:

Code:

CREATE procedure [dbo].[sp_RandomBanner]
AS
Select TOP 1 dtPostDate, vchrLinkURL, vchrImagePath, vchrImageName, 
                  vchrStatus,vchrChangeStatus 
FROM tblBanner 
WHERE vchrStatus='Running'
ORDER BY NEWID( )

Anyway, as GBianchi asked, do you want the banners to be sequential *PER USER* or sequential for *ALL* users???

And a *VERY* important question, that could make all this a lot simpler and a lot faster: How many banners are there??? [If there are only a few--say less than 100?--you could just put them into memory and forget using the DB and get much better efficiency. Especially easy to do with ASP and ASP.NET, which I'm guessing is what this is for, since you are using SQL Server.]