locked
case statement in dynamic sql not working RRS feed

  • Question

  • 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
    
    

    Saturday, February 25, 2012 8:22 PM

Answers

  • This is very difficult to look at.

    The best thing you can do is to do the following in your code in both the locations.

    -- exec (@sqlString)
    print (@sqlString)

    Replace exec with print. See the query that would fire and put it in SSMS window, try parsing it and SSMS will show you exactly where is the error.

    Post it in the forums if you have any issues further.

    Hope this helps.


    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu


    • Edited by arun.passioniway Saturday, February 25, 2012 8:59 PM typo edit.
    • Proposed as answer by Kalman Toth Saturday, February 25, 2012 9:10 PM
    • Marked as answer by Kalman Toth Saturday, March 3, 2012 9:01 PM
    Saturday, February 25, 2012 8:58 PM
  • First of all, why is the table a parameter in the first place? How many different tables are there? Can you make a view that unifies all tables?

    As you have discovered, writing dynamic SQL is difficult. If you don't apply strict discipline, you can easily get lost. So the first approach is to try to avoid having to use dynamic SQL at all. If there are only three tables, maybe it's better to write one procedure for each table?

    But if you use dynamic SQL, you should do it properly, amnd parameterise everything that is parameterisable.

    Take this:

    + ' 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 ))'

    Are you able to read this? I am not. On
    http://www.sommarskog.se/dynamic_sql.html#sp_executesql
    you can learn how to use sp_executesql and parameterised dynamic SQL. Study this. try the examples and play around, so that you understand the principles. Once you do this, you can clean up your code, and I would not be surprised if your problems resolves automatically.

    But again, do you really need dynamic SQL?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Sunday, February 26, 2012 7:15 PM
    • Marked as answer by Kalman Toth Saturday, March 3, 2012 9:01 PM
    Sunday, February 26, 2012 5:25 PM

All replies

  • As far as I can see, "end" is just at the wrong position: it should be after the first closing bracket.

    + ' = 10 then Plant_ID else (Select TOP 1 ru.Plant_ID from Fusion.Routing ru where ru.ID = Routing_Id) end) IS NULL ))';

    As a side note: I've added a TOP 1 just to make sure the select will always return one value.

    Saturday, February 25, 2012 8:30 PM
  • This is very difficult to look at.

    The best thing you can do is to do the following in your code in both the locations.

    -- exec (@sqlString)
    print (@sqlString)

    Replace exec with print. See the query that would fire and put it in SSMS window, try parsing it and SSMS will show you exactly where is the error.

    Post it in the forums if you have any issues further.

    Hope this helps.


    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu


    • Edited by arun.passioniway Saturday, February 25, 2012 8:59 PM typo edit.
    • Proposed as answer by Kalman Toth Saturday, February 25, 2012 9:10 PM
    • Marked as answer by Kalman Toth Saturday, March 3, 2012 9:01 PM
    Saturday, February 25, 2012 8:58 PM
  • First of all, why is the table a parameter in the first place? How many different tables are there? Can you make a view that unifies all tables?

    As you have discovered, writing dynamic SQL is difficult. If you don't apply strict discipline, you can easily get lost. So the first approach is to try to avoid having to use dynamic SQL at all. If there are only three tables, maybe it's better to write one procedure for each table?

    But if you use dynamic SQL, you should do it properly, amnd parameterise everything that is parameterisable.

    Take this:

    + ' 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 ))'

    Are you able to read this? I am not. On
    http://www.sommarskog.se/dynamic_sql.html#sp_executesql
    you can learn how to use sp_executesql and parameterised dynamic SQL. Study this. try the examples and play around, so that you understand the principles. Once you do this, you can clean up your code, and I would not be surprised if your problems resolves automatically.

    But again, do you really need dynamic SQL?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Sunday, February 26, 2012 7:15 PM
    • Marked as answer by Kalman Toth Saturday, March 3, 2012 9:01 PM
    Sunday, February 26, 2012 5:25 PM