I have store procedure in sql server, it have many allow null parameters, except @Action:
ALTER Procedure [dbo].[pDepartmentCRUD]
@Action varchar(10),
@AreaID int = null,
@DeptID int = null,
@DeptName nvarchar(100) = null,
@DeptAddress nvarchar(300) = null,
@DeptPhone nvarchar(50) = null,
@AreaName nvarchar(100) = null
AS
BEGIN
SET NOCOUNT ON;
--SELECT
IF @Action= 'SELECT'
IF @AreaName IS NOT NULL and @DeptName IS NOT NULL -- search Dept by DeptName or AreaName
BEGIN
Select DeptID,DeptName,DeptAddress,DeptPhone, AreaName, d.AreaID
from Department d inner join Area a on d.AreaID = a.AreaID
where AreaName like '%' + @AreaName + '%' or DeptName like '%' + @DeptName + '%'
END
ELSE IF @AreaID IS NOT NULL -- select by AreaID
BEGIN
Select DeptID,DeptName,DeptAddress,DeptPhone, AreaName, d.AreaID
from Department d inner join Area a on d.AreaID = a.AreaID
where d.AreaID = @AreaID
END
ELSE
BEGIN
Select DeptID,DeptName,DeptAddress,DeptPhone, AreaName, d.AreaID
from Department d inner join Area a on d.AreaID = a.AreaID
END
......................................
in Controller, i must call it with many null parameters:
var tr = TestEquipDBEntities.pDepartmentCRUD("SELECT", null, null, null, null, null, null).ToList();
how can i call it with like call procedure with only @Action parameter, like in sql:
exec pDepartmentCRUD @Action = 'SELECT'