quote:

---

CREATE PROCEDURE sp_GetRandomProduct






(
@i int
)
AS

declare @nRecordCount int
declare @nRandNum int
declare @LikeTeamID int
declare @productID int









-- Create a temporary table with the same structure of
-- the table we want to select a random record from
CREATE TABLE #TempTable
(
ProdID int,
TeamID int,
idNum int identity(1,1)
)



--Get the Team ID of the main product

Select @LikeTeamID = products.TeamID
FROM products
WHERE products.ProdID = @i



-- Dump the contents of the table to seach into the
-- temp. table

INSERT INTO #TempTable
SELECT products.TeamID, products.ProdID
FROM products
WHERE products.TeamID = @LikeTeamID










-- Get the number of records in our temp table
Select @nRecordCount = count(*) From #TempTable






-- Select a random number between 1 and the number
-- of records in our table
Select @nRandNum = Round(((@nRecordCount - 2) * Rand() + 1), 0)





-- Select the record from the temp table with the
-- ID equal to the random number selected...
Select @productID = ProdID From #TempTable
Where idNum = @nRandNum





SELECT products.ProdID, products.Product_Type, products.Brand, products.Product_Name, products.Picture_ID_T, Prod_Supply.Price, Team_names.Team_Name, CitySt_Tbl.citystate_name
FROM (Team_names INNER JOIN (CitySt_Tbl INNER JOIN products ON CitySt_Tbl.City_St_ID = products.CityStID) ON Team_names.TeamID = products.TeamID) INNER JOIN Prod_Supply ON products.ProdID = Prod_Supply.ProdID
Where products.ProdID = @productID

---