asked on

SQL Server: IF-Blocks do not appear to be evaluating varchar value correctly.

When this stored proc executes, it doesn't seem to be able to evaluate whether the variable @SQLAgentJobType contains the text 'Incremental' or 'Manual'. Right now the value is set in the SSIS environment as 'Manual', but it executed the steps for 'Incremental'. If I reverse the order of the IF -blocks, it will execute the steps for 'Manual' first if the value is set to 'Incremental'. As an aside, before anyone complains about the start and end dates as being nvarchar(20)-- don't. They are set this way because they are concatenated to another set of SSIS environment variables for an openquery statement needed elsewhere in the SSIS package. Please review and advise.

ALTER PROCEDURE [etl].[ResetProjectDaterangeVariables]
	-- Add the parameters for the stored procedure here
( @SQLAgentJobType varchar(20),
  @FolderName nvarchar(128),
  @EnvironmentName nvarchar(128),
  @startDate nvarchar(20),
  @endDate nvarchar(20)
)
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.

	SET NOCOUNT ON;

	 DECLARE
	 -- Note: The string dates are created in order to deal with cobbling together an Informix SQL statement as part of a 
	 --       concatenated text string built by variables used in source dataflows

	 @tempstartDate nvarchar(20)
	,@tempendDate nvarchar(20)
	-- leave room for a leading space
	,@fixedEndTime nvarchar(9)
	,@fixedStartTime nvarchar(9)
	,@var sql_variant;

   -- DEBUG [uncomment]
   --  declare @SQLAgentJobType varchar(20)
   --  set @SQLAgentJobType = 'Incremental'

   -- set the fixed timestamps for beginning of day and of day
   set @fixedEndTime =   N' 23:59:59'
   set @fixedStartTime = N' 00:00:00'

   IF (@SQLAgentJobType = 'Incremental')
     BEGIN
	     set @tempstartDate = convert(nvarchar(10), (dateadd(d,-1,CURRENT_TIMESTAMP)),126) + @fixedStartTime
             set @tempendDate   = convert(nvarchar(10), current_timestamp,126) + @fixedEndTime

	     set @var = @tempstartDate
	     EXEC [ssisdb].[catalog].[set_environment_variable_value]
	    @variable_name=N'startDate'
	   ,@environment_name = @EnvironmentName
	   ,@folder_name = @FolderName
	   ,@value = @var;

	     set @var = @tempendDate
	    EXEC [ssisdb].[catalog].[set_environment_variable_value]
	   @variable_name=N'endDate'
	  ,@environment_name = @EnvironmentName
	  ,@folder_name = @FolderName
	  ,@value = @var;

      END

      IF (@SQLAgentJobType = 'Manual')
	   BEGIN

	   		set @var = @startDate
			EXEC [ssisdb].[catalog].[set_environment_variable_value]
			@variable_name=N'startDate'
			,@environment_name = @EnvironmentName
			,@folder_name = @FolderName
			,@value = @var;

			set @var = @endDate
			EXEC [ssisdb].[catalog].[set_environment_variable_value]
			@variable_name=N'endDate'
			,@environment_name = @EnvironmentName
			,@folder_name = @FolderName
			,@value = @var;

	   END

  

END

Open in new window

David Johnson, CD

OP code

IF (@SQLAgentJobType = 'Incremental')
     BEGIN
/* stuff */
      END

      IF (@SQLAgentJobType = 'Manual')
	   BEGIN
/* stuff */
	   	
	   END

Open in new window

suggested solution

...

IF (@SQLAgentJobType = 'Incremental')
     BEGIN
         /* your stuffs */
     END

ELSE
     IF (@SQLAgentJobType = 'Manual')
	   BEGIN
                 /* your stuffs */
           END

....

Open in new window

What is the difference?

ASKER CERTIFIED SOLUTION

Paul Jackson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rajeev

Hi,

Other then structure how to write the code, your code looks fine.

And there is no such issue with SQL to interpret varchar wrongly. I can assure you that please look into the calling procedure that gives a call to

exec [etl].[ResetProjectDaterangeVariables]

Open in new window

Your variable value is getting changed there only. If you still find it doing wrong then do let us know.

Scott Pletcher

IF there's any change the db is case sensitive, or just to avoid potential issues if it is, do this:

...
IF (LOWER(@SQLAgentJobType) = 'incremental')
...
IF (LOWER(@SQLAgentJobType) = 'manual')
...

That's the only thing potentially wrong I see. I don't see anything that could give you the specific kind of problem you described, though.

Paula DiTallo

ASKER

To everyone kind enough to respond, thanks so much for all of your help. Paul's restructuring of the IF-Block ultimately solved the fall-out issue. Rjeev's suggestion to look at the calling of the stored proc with the passed variables was helpful, however, in this case the call with the variables was not at fault.