Hello friends,
SubMenu Designation datatype is nvarchar(max)
JOB REQUISITION REPORT CEO
FULL AND FINAL UPDATION CEO, CSO, TSO
QUALIFICATION MASTER NULL
CREATE DEPARTMENT NULL
TERMINATION REPORT CEO, CSO,
CONSULTANT MASTER CEO, CSO,
Problem is that my table column Designation single row contains multiple designation name
and in my parameter I pass only one designation. I have to retrive the those rows which designation column contains Parameter value. My procedure return all columns why ?
sql = "usp_HRM_GetMenuToBindMenu";
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Designation", SessionManager.DesignationName); is ''TSO' I m sending
SqlDataAdapter dap = new SqlDataAdapter(cmd);
dap.Fill(ds);
cmd.ExecuteNonQuery();
dt = ds.Tables[0];
I want to fetch those menus whose designation column contain TSO
How to write procedure
my procedure is below. I fetch record only one record. how to do it
ALTER PROC usp_HRM_GetMenuToBindMenuuuu
(
@Designation NVARCHAR(MAX)
)
AS
BEGIN
SELECT sm.Designation, mm.MainMenuID,
mm.MainMenu,
mm.MContent,
mm.MenuOrder,
mm.IsActive[IsActiveMain],
sm.ParentID,
sm.SubMenu,
sm.MenuOrder[SubMenuOrder],
sm.SubMenuID,
sm.[Content][Sub Menu Content],
sm.SubURL,
sm.IsActive[IsActiveSub]
FROM MainMenu mm(NOLOCK)
INNER JOIN SubMenu sm(NOLOCK)
ON sm.ParentID = mm.MainMenuID
WHERE --sm.IsActive = 1
@Designation IN(SELECT sm.Designation FROM SubMenu sm(NOLOCK) WHERE sm.Designation IS NOT NULL)
--AND sm.Designation IN (@Designation)
ORDER BY
mm.MenuOrder ASC,
sm.MenuOrder ASC
END