USE [DineShore]
GO
/****** Object: StoredProcedure [dbo].[spDesignation_GetDetails] Script Date: 04/19/2013 11:17:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* ==========================================
-- Purpose : To Select Department Details
-- Module : DineShore Admin
-- Created By : Sumesh
-- Created On : 10- Feb - 2012 09:00 AM
-- Execute : [spDesignation_GetDetails] null,1,null,2, 2,2,'Code',NULL
-- Conditions : IF pass @P_OrganizationID then return all details that under passing OrgID
@P_User For Check user have Permission for that record, Check That user Entered or not
ID Status Remarks
NULL NULL Return All Designation Details
NULL True Return All Designation Details with Active Status
NULL False Return All Designation Details with InActive Status
ID True Return Designation Details with ID and Active Status
ID False Return Designation Details with ID and InActive Status
ID NULL Return Designation Detail by ID
-- Returns : Designation Details with data table
==========================================*/
ALTER PROC [dbo].[spDesignation_GetDetails]
(@P_ID INT = NULL
,@P_User INT
,@P_Status BIT = NULL
,@P_OrganizationID INT = NULL
,@P_PageSize INT = NULL
,@P_Skip_Value INT = NULL
,@P_SearchColumn VARCHAR(100) = NULL
,@P_SearchValue VARCHAR(250) = NULL)
AS
BEGIN
IF(@P_PageSize IS NULL)
BEGIN
SELECT D.[Id]
,D.[Code]
,D.[Name]
,D.[Remarks]
,D.[OrganizationID]
,O.Name AS [OrganizationName]
,D.[CreatedBy]
,D.[CreatedDt]
,D.[Status]
,Case When D.[CreatedBy] in (@P_User) Then 1
Else 0 End AS [Mode]
FROM [Designations_Mst] AS D
INNER JOIN [Organization_Mst] AS O ON D.[OrganizationID] = O.ID
WHERE D.ID =ISNULL(@P_ID,D.ID) AND D.[Status] =ISNULL(@P_Status,D.[Status])
AND D.[OrganizationID] = ISNULL (@P_OrganizationID,D.[OrganizationID]) ORDER BY D.[Name]
END
ELSE
BEGIN
DECLARE @sql NVARCHAR(MAX);
DECLARE @Wheresql NVARCHAR(MAX);
SET @sql= 'SELECT TOP '+ CONVERT(VARCHAR(25),@P_PageSize) + ' D.[Id]
,D.[Code]
,D.[Name]
,D.[Remarks]
,D.[OrganizationID]
,O.Name AS [OrganizationName]
,D.[CreatedBy]
,D.[CreatedDt]
,D.[Status]
,CASE WHEN D.[CreatedBy] in ('+ CONVERT(VARCHAR(25),@P_User)+') THEN 1
ELSE 0 END AS [Mode]
FROM [Designations_Mst] AS D
INNER JOIN [Organization_Mst] AS O ON D.[OrganizationID] = O.ID
WHERE D.[ID] NOT IN (SELECT TOP '+ CONVERT(VARCHAR(25),@P_Skip_Value) + ' DS.[Id] FROM [Designations_Mst] AS DS ORDER BY DS.[ID] DESC )'
IF (@P_SearchColumn = 'OrganizationID')
BEGIN
SET @Wheresql =' AND D.[OrganizationID] IN (Select O.[ID] from [Organization_Mst] AS O WHERE O.[Name] LIKE '''+ '%' + ISNULL(@P_SearchValue, '%') + '%' + '''' +')'
END
ELSE
BEGIN
SET @Wheresql =' AND '+ ISNULL (' D.'+@P_SearchColumn, 'D.[Code]')+ ' LIKE '''+ '%' + ISNULL(@P_SearchValue, '%') + '%' + ''''
END
SET @sql=@sql+@Wheresql+ ' ORDER BY D.[ID] DESC'
EXEC sp_executesql @sql
END
END
GO
/****** Object: StoredProcedure [dbo].[spDesignation_GetDetails] Script Date: 04/19/2013 11:17:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* ==========================================
-- Purpose : To Select Department Details
-- Module : DineShore Admin
-- Created By : Sumesh
-- Created On : 10- Feb - 2012 09:00 AM
-- Execute : [spDesignation_GetDetails] null,1,null,2, 2,2,'Code',NULL
-- Conditions : IF pass @P_OrganizationID then return all details that under passing OrgID
@P_User For Check user have Permission for that record, Check That user Entered or not
ID Status Remarks
NULL NULL Return All Designation Details
NULL True Return All Designation Details with Active Status
NULL False Return All Designation Details with InActive Status
ID True Return Designation Details with ID and Active Status
ID False Return Designation Details with ID and InActive Status
ID NULL Return Designation Detail by ID
-- Returns : Designation Details with data table
==========================================*/
ALTER PROC [dbo].[spDesignation_GetDetails]
(@P_ID INT = NULL
,@P_User INT
,@P_Status BIT = NULL
,@P_OrganizationID INT = NULL
,@P_PageSize INT = NULL
,@P_Skip_Value INT = NULL
,@P_SearchColumn VARCHAR(100) = NULL
,@P_SearchValue VARCHAR(250) = NULL)
AS
BEGIN
IF(@P_PageSize IS NULL)
BEGIN
SELECT D.[Id]
,D.[Code]
,D.[Name]
,D.[Remarks]
,D.[OrganizationID]
,O.Name AS [OrganizationName]
,D.[CreatedBy]
,D.[CreatedDt]
,D.[Status]
,Case When D.[CreatedBy] in (@P_User) Then 1
Else 0 End AS [Mode]
FROM [Designations_Mst] AS D
INNER JOIN [Organization_Mst] AS O ON D.[OrganizationID] = O.ID
WHERE D.ID =ISNULL(@P_ID,D.ID) AND D.[Status] =ISNULL(@P_Status,D.[Status])
AND D.[OrganizationID] = ISNULL (@P_OrganizationID,D.[OrganizationID]) ORDER BY D.[Name]
END
ELSE
BEGIN
DECLARE @sql NVARCHAR(MAX);
DECLARE @Wheresql NVARCHAR(MAX);
SET @sql= 'SELECT TOP '+ CONVERT(VARCHAR(25),@P_PageSize) + ' D.[Id]
,D.[Code]
,D.[Name]
,D.[Remarks]
,D.[OrganizationID]
,O.Name AS [OrganizationName]
,D.[CreatedBy]
,D.[CreatedDt]
,D.[Status]
,CASE WHEN D.[CreatedBy] in ('+ CONVERT(VARCHAR(25),@P_User)+') THEN 1
ELSE 0 END AS [Mode]
FROM [Designations_Mst] AS D
INNER JOIN [Organization_Mst] AS O ON D.[OrganizationID] = O.ID
WHERE D.[ID] NOT IN (SELECT TOP '+ CONVERT(VARCHAR(25),@P_Skip_Value) + ' DS.[Id] FROM [Designations_Mst] AS DS ORDER BY DS.[ID] DESC )'
IF (@P_SearchColumn = 'OrganizationID')
BEGIN
SET @Wheresql =' AND D.[OrganizationID] IN (Select O.[ID] from [Organization_Mst] AS O WHERE O.[Name] LIKE '''+ '%' + ISNULL(@P_SearchValue, '%') + '%' + '''' +')'
END
ELSE
BEGIN
SET @Wheresql =' AND '+ ISNULL (' D.'+@P_SearchColumn, 'D.[Code]')+ ' LIKE '''+ '%' + ISNULL(@P_SearchValue, '%') + '%' + ''''
END
SET @sql=@sql+@Wheresql+ ' ORDER BY D.[ID] DESC'
EXEC sp_executesql @sql
END
END
No comments:
Post a Comment