How do I use ModifySqlQuery to pass 2 separate values to 2 separate WHERE clauses? - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

How do I use ModifySqlQuery to pass 2 separate values to 2 separate WHERE clauses?

Last post 02-11-2016 12:41 PM by Mfranco. 3 replies.
Page 1 of 1 (4 items)
Sort Posts: Previous Next
  • 02-11-2016 10:35 AM

    How do I use ModifySqlQuery to pass 2 separate values to 2 separate WHERE clauses?

    DECLARE @emps AS TABLE (evc_code varchar(max)) INSERT INTO @emps (evc_code) SELECT DISTINCT evc_code FROM prj_detail WHERE (fiscal_year = @year) AND (evc_type_code = 'E') AND (rev_type_code = '01') DECLARE @names AS TABLE (emp_code varchar(max), emp_name varchar(max)) INSERT INTO @names (emp_code, emp_name) SELECT emp_info.emp_code, emp_info.emp_name FROM emp_info INNER JOIN emp_group_map ON emp_info.emp_code = emp_group_map.emp_code INNER JOIN emp_group_code ON emp_group_map.rsrc_group_code = emp_group_code.emp_group_code WHERE (emp_group_code.emp_group_name = @BU) SELECT a.* FROM @names a INNER JOIN @emps b ON b.evc_code = a.emp_code The above is my query. I need to supply the @year and @BU for my where clauses. These values will be passed from an SSRS report. I've used this command before with a single parameter in a Single WHERE clause. But how do I go about using it for 2 separate values in 2 separate WHERE clauses in the same Query. I'm on the latest version of qRules.
  • 02-11-2016 10:40 AM In reply to

    Re: How do I use ModifySqlQuery to pass 2 separate values to 2 separate WHERE clauses?

    I tried to separate my code above from my actual comments. But none of the options for formatting are available and it's removing my hard returns, tabs and spaces.
  • 02-11-2016 12:24 PM In reply to

    Re: How do I use ModifySqlQuery to pass 2 separate values to 2 separate WHERE clauses?

    Yeah, anything but IE doesn't seem to work too well with the forum. Sorry about that. I think instead of using ModifySqlQuery, you should use ExecuteSqlQuery, and I think you should create a stored procedure and use ExecuteSqlQuery to pass your parameters into that. Set implicit_transactions to off in your stored proc.
    Hilary Stoupa

  • 02-11-2016 12:41 PM In reply to

    Re: How do I use ModifySqlQuery to pass 2 separate values to 2 separate WHERE clauses?

    My Dear.. I am already implementing that exact scenario. Rethought my process. I believe I have it nailed on this front. Working on the SSIS package now. Thanks again!
Page 1 of 1 (4 items)
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.