SP and SQL queries

[vaishalilambe17]

Hi,

I am trying to run below thing in input tool :

1. Select Distinct type_i  From master_personnel_dtls Where master_personnel_dtls.pln_i = 'ESOP'
2. 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
3. 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.

[jamielaird · Accepted solution]

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:

1. An Input tool
2. A Formula tool
3. 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.

[jamielaird]

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

[vaishalilambe17]

@jamielaird  Thank you so much for your reply. I am getting no columns returned. PFA my workflow

[vaishalilambe17 · Accepted solution]

@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?

[jamielaird · Accepted solution]

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).

[vaishalilambe17]

@jamielaird what is unique placeholder value?

[jamielaird · Accepted solution]

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.

[vaishalilambe17]

Thanks

[vaishalilambe17]

stilll have issue like PFA screenshot