There is a procedure [Fusion].[usp_ValidateDataPermissionforUpload] that has two dynamic sql strings assigned to the variable @sqlString depending on two 'If' conditions. I want to do this in one dynamic sql string but somehow not able to accomplish
it as of now.
The code block should go something like this:
IF @EnterpriseHierarchyName = N'Plant'
begin
--dynamic sql string starts
set @sqlString =
--the last line in dynamic sql string should be something like below:
+ ' AND ([Fusion].[ufn_CheckUserPermissionforUpload](' + @strUserID + ',' + cast(@EnterpriseHierarchyId as varchar(10)) + ',case when ' + cast(@ImportData_SubCategory_ID as varchar(10)) + ' = 10 then Plant_ID else (Select ru.Plant_ID from Fusion.Routing
ru where ru.ID = Routing_Id end)) IS NULL ))'
end
The case statement is not working. Can anybody please advise.
Here is the original procedure.
CREATE PROC [Fusion].[usp_ValidateDataPermissionforUpload]
@ImportData_SubCategory_ID INT,
@User_Id INT,
@FltTable VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sqlString NVARCHAR(max)
DECLARE @ErrorMsg VARCHAR(300)
DECLARE @strSubCatID VARCHAR(10)
DECLARE @strUserID VARCHAR(10)
DECLARE @IsPermissionRequired BIT
DECLARE @FieldName varchar(50);
DECLARE @Name varchar(50);
DECLARE @EnterpriseHierarchyId INT
DECLARE @EnterpriseHierarchyName VARCHAR(50)
DECLARE @ParmDefinition nvarchar(500);
DECLARE @RoutingTotal INT
DECLARE @IsSystemAdminUser BIT
SET @strSubCatID = CAST(@ImportData_SubCategory_ID AS VARCHAR(10));
SET @strUserID = CAST(@User_ID AS VARCHAR(10));
--check if user is system administrator as needs no permissions
set @IsSystemAdminUser = Fusion.ufn_IsSystemAdminUser(@User_ID)
If @IsSystemAdminUser = 0 --not a system admin
BEGIN
--check if permission is required on this import data subcategory (by calling function Fusion.ufn_CheckPermissionOnSubCategory)
set @IsPermissionRequired = Fusion.ufn_CheckPermissionOnSubCategory (@ImportData_SubCategory_ID)
--permission is required
IF(@IsPermissionRequired IS NULL)
BEGIN
-- get field names for subcategory on which permission is required
DECLARE cur_VM CURSOR FOR
SELECT
imdf.FieldName,
imdf.Name
FROM [Fusion].[ImportData_Fields] imdf
WHERE [ImportData_SubCategory_ID] = @ImportData_SubCategory_ID
and Permission = 1
ORDER BY sequence
OPEN cur_VM
FETCH NEXT FROM cur_VM INTO @FieldName,@Name;
WHILE @@FETCH_STATUS = 0
BEGIN
-- find if permission field is plant or department
select @EnterpriseHierarchyId = Id,
@EnterpriseHierarchyName = Name
from [Fusion].[EnterpriseHierarchyType]
where Name = Case @Name
when N'Routing Name' then N'Plant'
when N'Department Name' then N'Department'
when N'Plant Name' then N'Plant'
when N'Plant' then N'Plant'
end
-- check for for PLANT level permission on selected import data subcategory
IF @EnterpriseHierarchyName = N'Plant' and @ImportData_SubCategory_ID In (10) --Routings subcategory (10) flat table already has Plant_Id column so we dont need to fetch it from RoutingsIds
BEGIN
--pick item ids from flat table and find permissions on them for the user in permissions table
SET @ErrorMsg= 'User does not have upload permission for the Plant that this routing belongs to';
set @sqlString = 'Update ' + @FltTable + ' set validationMessage = case when len(COALESCE(ValidationMessage,''''))>0 then
ValidationMessage + ''|'' else '''' end +''' + @ErrorMsg +''' where id in
(SELECT ID FROM ' + @FltTable + ' WHERE ImportData_SubCategory_ID = '
+ cast(@ImportData_SubCategory_ID as varchar(10)) + ' and user_id='+@strUserID
+ ' AND ([Fusion].[ufn_CheckUserPermissionforUpload](' + @strUserID + ',' + cast(@EnterpriseHierarchyId as varchar(10)) + ',' + 'Plant_ID) IS NULL ))'
exec (@sqlString)
END
-- check for for PLANT (ROUTING LEVEL) level permission on selected import data subcategory
ELSE IF @EnterpriseHierarchyName = N'Plant' -- fetch plant id from routing ids for other then Routings subCategory (10)
BEGIN
--pick item ids from flat table and find permissions on them for the user in permissions table
SET @ErrorMsg= 'User does not have upload permission for the Plant that this routing belongs to';
set @sqlString = 'Update ' + @FltTable + ' set validationMessage = case when len(COALESCE(ValidationMessage,''''))>0 then
ValidationMessage + ''|'' else '''' end +''' + @ErrorMsg +''' where id in
(SELECT ID FROM ' + @FltTable + ' WHERE ImportData_SubCategory_ID = '
+ cast(@ImportData_SubCategory_ID as varchar(10)) + ' and user_id='+@strUserID
+ ' AND ([Fusion].[ufn_CheckUserPermissionforUpload](' + @strUserID + ',' + cast(@EnterpriseHierarchyId as varchar(10)) + ', (Select ru.Plant_ID from Fusion.Routing ru where ru.ID = Routing_Id)) IS NULL ))'
exec (@sqlString)
END
FETCH NEXT FROM cur_VM INTO @FieldName,@Name;
End
CLOSE cur_VM
DEALLOCATE cur_VM
END
END
END
GO
