July 16, 2002 at 7:32 am
i will be receiving 3 parameters to my stored. proc. If the parameter comes back empty, how can i remove it from the where clause completely. like if widthvalue = " ",
then (sizevalue = "7") and (colorvalue = "blue")
CREATE PROCEDURE GetProductSKU
(
@prmShortSku varchar(5),
@prmSizevalue varchar(25),
@prmColorvalue varchar(25),
@prmWidthvalue varchar(25),
@prmSKUResult varchar(50) OUTPUT
)
AS
set nocount on
SELECT @prmSKUResult = FSP.SKU
FROM FSProducts FSP
where
(sizevalue = @prmsizevalue)
and
(colorvalue = @prmcolorvalue)
and
(widthvalue = @prmwidthvalue)
GO
thanks for your help
</cm>
July 16, 2002 at 7:38 am
Could use: -
SELECT @prmSKUResult = FSP.SKU
FROM FSProducts FSP
where
(sizevalue = @prmsizevalue)
and
(colorvalue = @prmcolorvalue)
and
case
when @prmwidthvalue is null then @prmwidthvalue
else widthvalue
end = @prmwidthvalue
Repeat for the other 2 parameters. The other alternative is to use dyanamic SQL, build the sql statement string dynamically and execute via sp_executesql or exec(@str).
Regards,
Andy Jones
.
July 16, 2002 at 7:52 am
Also you will need to set the variable with a default value when nothing is submitted or it will return an error.
Ex.
@prmWidthvalue varchar(25) = "",
Then you can further what andy suggested and ensure it nothing or "" submitted it is handled like so
case
when @prmwidthvalue = "" then @prmwidthvalue
else widthvalue
end = @prmwidthvalue
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 16, 2002 at 8:49 am
works beautifully. thanks for the always quick responses. this is the best community.
</cm>
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply