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 >=@keyid
or 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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy