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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-10-24 : 09:32:09
|
Michael writes "hi team
SQL Server 2000 Enterprise Edition, Win2000 Server (SP2)
I need to produce a random number (between 1 and N) and return it in the result set, then use that result to perform a query.
Here's the scenario:
I have built a few tables to handle banner ads on a web site, and I want to randomly select one of the banner ID's, return that number, and then query that specific row out to display the banner, url, etc...
Can you help me out?
I have attempted to use RAND(), but cannot return a number set to use in this case. I need to be able to specify the random number range (1 to N) as variable:
DECLARE @minNum int
DECLARE @maxNum int
SET @minNum = 1
SET @maxNum = (SELECT MAX(url_id) FROM tblAdClickURL)
I would also like to guarantee that the result number is a valid ID, and that it's "row_status" is equal to 1 (see table construct below) before selecting that ID for execution.
Here's the table structure:
adclick_id is a FK from another table (just insert a 1 or any number there)
/****************************************************
tblAdClickURL
****************************************************/
CREATE TABLE tblAdClickURL (
url_id int NOT NULL,
adclick_id int NOT NULL,
url_banner varchar(256) NOT NULL,
url_location varchar(256) NOT NULL,
last_update_dt datetime NOT NULL DEFAULT GETDATE(),
row_status bit NOT NULL DEFAULT 0,
row_guid uniqueidentifier NOT NULL DEFAULT NEWID(),
row_timestamp timestamp NOT NULL
)
go
ALTER TABLE tblAdClickURL
ADD PRIMARY KEY (url_id)
go
/****************************************************
tblAdClickURL
****************************************************/
CREATE PROCEDURE dbo.sp_insert_adclickurl
@adclick_id int,
@url_banner varchar(256),
@url_location varchar(256)
AS
SET NOCOUNT ON
BEGIN
DECLARE @next_id int
SELECT @next_id = IsNull(MAX(url_id),0)+1 FROM tblAdClickURL
BEGIN TRAN
INSERT INTO tblAdClickURL
(
url_id,
adclick_id,
url_banner,
url_location,
last_update_dt,
row_status,
row_guid
)
VALUES
(
@next_id,
@adclick_id,
@url_banner,
@url_location,
getdate(),
1,
newid()
)
IF (@@ERROR!=0)
BEGIN
RAISERROR 20002 '<<<<< INSERT ERROR ON PROCEDURE sp_insert_adclickurl: CANNOT INSERT NEW ROW >>>>>'
ROLLBACK TRAN
RETURN(1)
END
COMMIT TRAN
END
GO
Thanks for any direction on this!
Michael
" |
|
|
|
|
|
|
|
random number result