January 14, 2009 at 5:00 pm
I have a stored procedure that runs fine by itself. However I want to run the same query with a fifferent wher clause depending on value of a parameter (@paramhome)
I know how to do if statment like if @paramhome = 'ALL' then select ...
If @paramhome <> 'ALL' the select ...
however the sproc i have multiple if statements and I do not get the results I expect when using multiple if statements
if i just run the queries separately without the @paramhome in the if statements they work fine
here is my sproc
ALTER PROCEDURE [dbo].[homes]
(
@ownerID UNIQUEIDENTIFIER,
@pageNum INT = 1,
@pageSize INT = 10,
@ParamHome varchar (50)
)
AS
DECLARE @rows INT
DECLARE @keyid INT
DECLARE @rowCount FLOAT /* yes we need a float for the math */
IF @pageNum = 1 and @ParamHome ='ALL'
BEGIN
SET @keyid=0
END
ELSE IF @ParamHome ='ALL'
BEGIN
/* get the values for the date and row */
SELECT @rows = (@pageNum-1) * @pageSize
SET ROWCOUNT @rows
SELECT @keyid=Albums.albumid FROM albums
WHERE(Albums.isprivate = 0) OR (Albums.isprivate = 1) AND (Albums.ownerid = @ownerid)
ORDER BY albumid ASC
END
SELECT @rowCount=COUNT(*) FROM albums WHERE (Albums.isprivate = 0) OR (Albums.isprivate = 1) AND (Albums.ownerid = @ownerid)
SET ROWCOUNT @pageSize
SELECT Albums.albumid, Albums.title, Albums.isprivate, MIN(Image.id) AS DisplayImage, COUNT(Image.id) AS ImageCount, Homes.Bedrooms,
Homes.FloorPlan, Homes.Bath, Homes.HouseType, Homes.Hdimensions, Homes.FirePlaceType,
Homes.Kitchen, Homes.NewKitchen,homes.bath,
Homes.SalePending, Homes.Sold
FROM Albums LEFT OUTER JOIN
Homes ON Albums.HouseID = Homes.HouseID LEFT OUTER JOIN
Image ON Albums.albumid = Image.album
WHERE (Albums.isprivate = 0) AND (Albums.albumid > @keyid) OR
(Albums.isprivate = 1) AND (Albums.albumid > @keyid) AND (Albums.ownerid = @ownerid)
GROUP BY Albums.albumid, Albums.title, Albums.isprivate, Homes.Bedrooms,
Homes.FloorPlan, Homes.Bath, Homes.HouseType, Homes.Hdimensions,
Homes.FirePlaceType, Homes.Kitchen,Homes.newKitchen,homes.salepending,homes.sold,homes.bath
RETURN CEILING(@rowCount/@pageSize)
IF @pageNum = 1 and @ParamHome <>'ALL'
BEGIN
SET @keyid=0
END
ELSE IF @ParamHome <>'ALL'
BEGIN
/* get the values for the date and row */
SELECT @rows = (@pageNum-1) * @pageSize
SET ROWCOUNT @rows
SELECT @keyid=Albums.albumid FROM albums
WHERE(Albums.isprivate = 0) OR (Albums.isprivate = 1) AND (Albums.ownerid = @ownerid)
ORDER BY albumid ASC
END
SELECT @rowCount=COUNT(*) FROM albums WHERE (Albums.isprivate = 0) OR (Albums.isprivate = 1) AND (Albums.ownerid = @ownerid)
SET ROWCOUNT @pageSize
SELECT Albums.albumid, Albums.title, Albums.isprivate, MIN(Image.id) AS DisplayImage, COUNT(Image.id) AS ImageCount, Homes.Bedrooms,
Homes.FloorPlan, Homes.Bath, Homes.HouseType, Homes.Hdimensions, Homes.FirePlaceType,
Homes.Kitchen, Homes.NewKitchen,homes.bath,
Homes.SalePending, Homes.Sold
FROM Albums LEFT OUTER JOIN
Homes ON Albums.HouseID = Homes.HouseID LEFT OUTER JOIN
Image ON Albums.albumid = Image.album
WHERE homes.housetype = @ParamHome and (Albums.isprivate = 0) AND (Albums.albumid > @keyid) OR
(Albums.isprivate = 1) AND (Albums.albumid > @keyid) AND (Albums.ownerid = @ownerid)
GROUP BY Albums.albumid, Albums.title, Albums.isprivate, Homes.Bedrooms,
Homes.FloorPlan, Homes.Bath, Homes.HouseType, Homes.Hdimensions,
Homes.FirePlaceType, Homes.Kitchen,Homes.newKitchen,homes.salepending,homes.sold,homes.bath
RETURN CEILING(@rowCount/@pageSize)
January 14, 2009 at 7:30 pm
I suggest that you check your logic. I suspect that you expect some code to conditionally execute but it doesn't because you are not using BEGIN/END blocks for the code in the IF/ELSE statements
January 14, 2009 at 11:16 pm
You dont need 2 different IF ELSE as the value of @keyid doesn't depend on the value of @ParamHome.
Also,you don't need 2 different select statements for @ParamHome = 'ALL' and @ParamHome <> 'ALL'. It can be done with one using the following 2 conditions (homes.housetype = @ParamHome OR 'ALL' = @ParamHome)
Try the below code and let us know the results.
ALTER PROCEDURE [dbo].[homes]
(
@ownerID UNIQUEIDENTIFIER,
@pageNum INT = 1,
@pageSize INT = 10,
@ParamHome varchar (50)
)
AS
DECLARE @rows INT
DECLARE @keyid INT
DECLARE @rowCount FLOAT /* yes we need a float for the math */
IF @pageNum = 1
BEGIN
SET @keyid=0
END
ELSE
BEGIN
/* get the values for the date and row */
SELECT @rows = (@pageNum-1) * @pageSize
SET ROWCOUNT @rows
SELECT @keyid=Albums.albumid FROM albums
WHERE(Albums.isprivate = 0) OR (Albums.isprivate = 1) AND (Albums.ownerid = @ownerid)
ORDER BY albumid ASC
END
SELECT @rowCount=COUNT(*) FROM albums WHERE (Albums.isprivate = 0) OR (Albums.isprivate = 1) AND (Albums.ownerid = @ownerid)
SET ROWCOUNT @pageSize
SELECT Albums.albumid, Albums.title, Albums.isprivate, MIN(Image.id) AS DisplayImage, COUNT(Image.id) AS ImageCount, Homes.Bedrooms,
Homes.FloorPlan, Homes.Bath, Homes.HouseType, Homes.Hdimensions, Homes.FirePlaceType,
Homes.Kitchen, Homes.NewKitchen,homes.bath,
Homes.SalePending, Homes.Sold
FROM Albums LEFT OUTER JOIN
Homes ON Albums.HouseID = Homes.HouseID LEFT OUTER JOIN
Image ON Albums.albumid = Image.album
WHERE (homes.housetype = @ParamHome OR 'ALL' = @ParamHome) and (Albums.isprivate = 0) AND (Albums.albumid > @keyid) OR
(Albums.isprivate = 1) AND (Albums.albumid > @keyid) AND (Albums.ownerid = @ownerid)
GROUP BY Albums.albumid, Albums.title, Albums.isprivate, Homes.Bedrooms,
Homes.FloorPlan, Homes.Bath, Homes.HouseType, Homes.Hdimensions,
Homes.FirePlaceType, Homes.Kitchen,Homes.newKitchen,homes.salepending,homes.sold,homes.bath
RETURN CEILING(@rowCount/@pageSize)
-Vikas Bindra
January 15, 2009 at 4:44 am
I'll check it out later today, I was going to add another statement to my where clause with the @paramhome parameter will i be able to do something like this??
WHERE (homes.housetype = @ParamHome OR 'ALL' = @ParamHome) and albumid >=@keyid
or homes.bedrooms >=@ParamHome and albumid>=@keyid...
January 15, 2009 at 1:59 pm
Gail (1/15/2009)
I'll check it out later today, I was going to add another statement to my where clause with the @paramhome parameter will i be able to do something like this??
WHERE (homes.housetype = @ParamHome OR 'ALL' = @ParamHome) and albumid >=@keyidor homes.bedrooms >=@ParamHome and albumid>=@keyid...
Make sure you clearly bracket your logic
Above code looks very confusing.... OR and AND's
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply