October 7, 2008 at 4:19 am
Hi All,
Please see the procedure below,
CREATE PROCEDURE DBO.GetDetailsByMenuID
(
@iGroupId INT=NULL,
@iMenuId INT=NULL
)
AS
BEGIN
/*
Written By: Syed Sanaullah Khadri
Date: 10/07/2008
EXEC GetDetailsByMenuID 101
EXEC GetDetailsByMenuID 101,155
SELECT * FROM MainMenu
SELECT * FROM Groups
*/
SET NOCOUNT ON
IF @iMenuId=NULL
BEGIN
SELECT mm.iMenuId,mm.vMenuTitle,mm.iParentId,mm.iOrder,mm.vRefTable,MM.bArchive,g.valign
FROM MainMenu MM
JOIN groups g ON g.iGroupId=mm.iGroupId
WHERE g.iGroupId=@iGroupId AND mm.iMenuId=mm.iParentId
ORDER BY iOrder
END
ELSE
BEGIN
SELECT mm.iMenuId,mm.vMenuTitle,mm.iParentId,mm.iOrder,mm.vRefTable,MM.bArchive,g.valign
FROM MainMenu mm JOIN Groups g ON g.iGroupId=mm.iGroupId
WHERE g.iGroupId=@iGroupId AND iParentId=@iMenuId AND iMenuId<>@iMenuId
ORDER BY iOrder
END
SET NOCOUNT OFF
END
When i am passing the iGroupId and iMenuId its giving the output, but when iMenuId as null the procedure is not giving and values but the query is giving the values. The Query is
SELECT mm.iMenuId,mm.vMenuTitle,mm.iParentId,mm.iOrder,mm.vRefTable,MM.bArchive,g.valign
FROM MainMenu MM
JOIN groups g ON g.iGroupId=mm.iGroupId
WHERE g.iGroupId=@iGroupId AND mm.iMenuId=mm.iParentId
ORDER BY iOrder
I dont know where i went wrong. Please help me....
Thanks in advance,
Syed Sanaullah Khadri
DBA
October 7, 2008 at 5:07 am
IF @iMenuId=NULL
That's probably yout problem, along with other uses of = or <> or other standard comparision operators with NULL value. This only works with ANSI NULLS OFF, and therefore it isn't recommended. Make sure you use IS NULL and IS NOT NULL instead. You need to use proper treatment of NULLs everywhere where they can appear
This will not work if either iMenuId or @iMenuId is NULL:
SELECT col1
FROM tableA
WHERE iMenuId=@iMenuId
If IMenuId is non-nullable column, but parameter can be null (meaning "show all"), this will do:
SELECT col1
FROM tableA
WHERE iMenuId=@iMenuId OR @iMenuId IS NULL
If both can be NULL, and NULL in the parameter means "show only rows with NULL", you can try this:
SELECT col1
FROM tableA
WHERE ISNULL(iMenuId, -1) = ISNULL(@iMenuId,-1)
.. based on assumption, that iMenuId is IDENTITY and therefore never really less than 0.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply