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

Paula DiTalloIntegration developerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David Johnson, CD, MVPOwnerCommented:
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?
0
Paul JacksonSoftware EngineerCommented:
Try simplifying to start with :

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

	 @startdatevar nvarchar(20)
	,@enddatevar 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 @startdatevar = convert(nvarchar(10), (dateadd(d,-1,CURRENT_TIMESTAMP)),126) + @fixedStartTime
        set @tenddatevar  = convert(nvarchar(10), current_timestamp,126) + @fixedEndTime
     END
   ELSE
     BEGIN
       IF (@SQLAgentJobType = 'Manual')
          BEGIN
             set @startdatevar = @startdate
             set @enddatevar = @enddate
          END
     END

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

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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rajeevnandanmishraCommented:
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.
0
Scott PletcherSenior DBACommented:
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.
0
Paula DiTalloIntegration developerAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.