stored procedure checking for null recordset, and if it is, select using less specific criteria

Question

Post reply

stored procedure checking for null recordset, and if it is, select using less specific criteria

LyndaPostal

SSC Journeyman

Points: 93
More actions
August 20, 2004 at 7:04 am

#152267

Hi
I am trying to get my stored procedure to return one of two (actually four) recordsets. Where I am checking to see if I am just looking for a specific manufacturer works (the first IF). But, if the first try (where I am checking to see if the client has set a flag, the nested IF ...) returns a null recordset (@@rowcount = 0) then I want the procedure to check for the same thing, but not looking for the flag.
This returns 2 recordsets if the 'bestsell' flag has not been set. The first is the null recordset (yeah, I know you experienced folks out there are going 'duh.') and then the good one. How do I get JUST the good recordset. I know I can check the array, but is there a way to get just what I want?
Thank you
Lynda
CREATE PROCEDURE dbo.StProc_GetPix
(
-- Parameter list
@sProductCategory nVarChar(50),
@sSubCategory nVarChar(25),
@sProductManufacturer nVarChar(50) = NULL
)
AS
/****** 18Aug04 Lp
client has two sites sharing the database. Only products with *CloutApparel* to show up on his apparel site and all products will show up on his *CloutRide*
so if the product manufacturer parameter is not passed, we are getting stuff for the CloutRide site. This procedure only gets one record so a representative image can be shown. If a flag is set, get that record. If no flag is set, get the newest record.
******/
set nocount on
IF @sProductManufacturer IS NULL
BEGIN
SELECT TOP 1 ThumbImage, ProductManufacturer,
ProductCategory, BestSell
FROM TblCartProducts
WHERE (ProductCategory = @sProductCategory)
AND (SubCat1 = @sSubCategory)
AND (BestSell = 1)
IF @@ROWCOUNT = 0
SELECT TOP 1 ThumbImage, ProductManufacturer,
ProductCategory, BestSell
FROM TblCartProducts
WHERE (ProductCategory = @sProductCategory) AND
(SubCat1 = @sSubCategory)
ORDER BY DateEntered
END
ELSE
BEGIN
SELECT TOP 1 ThumbImage, ProductManufacturer, ProductCategory, BestSell
FROM TblCartProducts
WHERE (ProductManufacturer = @sProductManufacturer)
AND (ProductCategory = @sProductCategory)
AND (SubCat1 = @sSubCategory)
AND (BestSell = 1)
IF @@ROWCOUNT = 0
SELECT TOP 1 ThumbImage, ProductManufacturer,
ProductCategory, BestSell
FROM TblCartProducts
WHERE (ProductManufacturer = @sProductManufacturer)
AND (ProductCategory = @sProductCategory)
AND (SubCat1 = @sSubCategory)
ORDER BY DateEntered
END
set nocount on
GO

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

dhilditch SSC-Addicted Points: 495 More actions · Answer 1

Rearrange order and check count(*)...

IF @sProductManufacturer IS NULL

BEGIN

IF (SELECT count(*)
    FROM TblCartProducts
    WHERE (ProductCategory = @sProductCategory)
    AND (SubCat1 = @sSubCategory)
    AND (BestSell = 1)) > 0
SELECT TOP 1 ThumbImage, ProductManufacturer,
ProductCategory, BestSell
FROM TblCartProducts
WHERE (ProductCategory = @sProductCategory)
AND (SubCat1 = @sSubCategory)
AND (BestSell = 1)
ELSE
SELECT TOP 1 ThumbImage, ProductManufacturer,
ProductCategory, BestSell
FROM TblCartProducts
WHERE (ProductCategory = @sProductCategory) AND
(SubCat1 = @sSubCategory)
ORDER BY DateEntered

END

Dave Hilditch.

LyndaPostal SSC Journeyman Points: 93 More actions · Answer 2

Dave, thank you so much. I was close, but not quite good to go. Such a small thing, such a big difference

Again, Thank you

Lynda