Hi, I am trying to run below thing in input tool : * Select Distinct type_i From master_personnel_dtls Where master_personnel_dtls.pln_i = 'ESOP' * SELECT CONVERT(DATETIME,CONVERT(VARCHAR(10),DATEADD(dd,-1*DAY(DATEADD(mm,-1,GETDATE()))+1,DATEADD(mm,-1,GETDATE())),101)) first_day_prior_month,DATEADD(ss,-1,CONVERT(DATETIME,CONVERT(VARCHAR(10),DATEADD(dd,DAY(GETDATE())*-1+1,GETDATE()),101))) last_day_prior_month * EXEC rpt_web_ord_exec_wrapper'XXXXX', '03/01/2019', '03/31/2019', 'CROSSPRODUCT', 'ABNH,AIM,BON,BONU,CONV,DCPL', 'REF_NO', '5' Output of first 2 is input for SP. Please see and guide how I can do it. I need to do it asap as I spent enough time finding out. No help found yet.

SP and SQL queries

Hi,

I am trying to run below thing in input tool :

Select Distinct type_i From master_personnel_dtls Where master_personnel_dtls.pln_i = 'ESOP'
SELECT CONVERT(DATETIME,CONVERT(VARCHAR(10),DATEADD(dd,-1*DAY(DATEADD(mm,-1,GETDATE()))+1,DATEADD(mm,-1,GETDATE())),101)) first_day_prior_month,DATEADD(ss,-1,CONVERT(DATETIME,CONVERT(VARCHAR(10),DATEADD(dd,DAY(GETDATE())*-1+1,GETDATE()),101))) last_day_prior_month
EXEC rpt_web_ord_exec_wrapper'XXXXX', '03/01/2019', '03/31/2019', 'CROSSPRODUCT', 'ABNH,AIM,BON,BONU,CONV,DCPL', 'REF_NO', '5'

Output of first 2 is input for SP. Please see and guide how I can do it. I need to do it asap as I spent enough time finding out. No help found yet.

Input

Database Connection

Accepted answers

jamielaird

Hi @vaishalilambe17

If I understand correctly you want to run two SELECT statements and then include the results of these in an EXEC statement that invokes a stored procedure.

To run the SELECT statements you simply need to configure two Input tools. Your second select statement doesn't actually reference a table and you are simply using SQL to work out the last day of the prior month. You can find simpler ways to do this using a Formula tool (the DateTime functions are well documented). So that leaves you with only one Input tool.

To invoke the stored procedure use a Dynamic Input tool. Again, this is well documented and you should be able to modify your SQL statement to include the values from the previous steps of the workflow.

Your final workflow will look something like this:

An Input tool
A Formula tool
A Dynamic Input tool

Since you're connecting to databases there's no way I can replicate this for you, but you will learn the most by doing this for yourself anyway. Please feel free to post up if you have questions as you build the workflow.

vaishalilambe17

@jamielaird

The thing is three values for the stored procedure are ffrom sql queries but remainig values are static. If i insert from formula toll it created those many records as my first query and table. PFA screenshot.

Also I am not understandinf, even though I am doing update sP for each column its giving me error opening procedure. Hw can I connect to SP n dynamic tool?

screenshot_query.PNG

jamielaird

Hi @vaishalilambe17

Your method looks OK to me, but you'll need to use unique placeholder values in the template SQL query within the Dynamic Input tool rather than NULL (since all of these will be replaced with the value of the first replacement).

Screenshot 2019-05-07 at 19.49.41.png

jamielaird

Something like 'value1', 'value2', 'value3' etc instead of 'NULL', 'NULL', 'NULL' etc.

When the SQL code is updated it is running a Find & Replace on the query text so you need to make sure you have a unique key for each value you want to replace.

All comments

jamielaird

Hi @vaishalilambe17

If I understand correctly you want to run two SELECT statements and then include the results of these in an EXEC statement that invokes a stored procedure.

Your final workflow will look something like this:

An Input tool
A Formula tool
A Dynamic Input tool

jamielaird

I couldn't resist ... here's how you'll find the last day of the prior month using a Formula tool:

Screenshot 2019-05-07 at 18.27.33.png

Quick Links

Unanswered
Community Events
Groups

This months top contributors

atcodedog05 19458

Qiu 15865

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703