Support Portal

for ProcessRobot and WinAutomation

Start a new topic
Answered

execute-sql-statement-input-parameter-datatable-on-storedprocedure

How to execute-sql-statement-input-parameter-datatable-on-storedprocedure


Best Answer

Hi Pavithra, 


Thank you for contacting us!


Since stored procedures cannot handle parameters as datatables (because in sql you'll have to declare variables as "TABLE" before using them) you could proceed with the following scenario as a workaround:


CREATE PROCEDURE test
AS
DECLARE @table TABLE
(
id INT,
id2 INT,
id3 INT
)
INSERT INTO @table(id, id2, id3)
select * from [dbo].[TEMP];
SELECT * FROM @table;


In case you want to use a declared TABLE variable


or directly 


CREATE PROCEDURE test
AS
select * from [dbo].[TEMP];


To make it simpler.



Either way you'll have to use a "TEMP" table in your sql server that will be updated each time you have a different WA table variable. By running a simple "INSERT" and/or "DELETE" querry, you could update your table accordingly. 

You can use the "execute sql statement" action to update your temp table.


I hope this helps!


1 Comment

ADMIN
Answer

Hi Pavithra, 


Thank you for contacting us!


Since stored procedures cannot handle parameters as datatables (because in sql you'll have to declare variables as "TABLE" before using them) you could proceed with the following scenario as a workaround:


CREATE PROCEDURE test
AS
DECLARE @table TABLE
(
id INT,
id2 INT,
id3 INT
)
INSERT INTO @table(id, id2, id3)
select * from [dbo].[TEMP];
SELECT * FROM @table;


In case you want to use a declared TABLE variable


or directly 


CREATE PROCEDURE test
AS
select * from [dbo].[TEMP];


To make it simpler.



Either way you'll have to use a "TEMP" table in your sql server that will be updated each time you have a different WA table variable. By running a simple "INSERT" and/or "DELETE" querry, you could update your table accordingly. 

You can use the "execute sql statement" action to update your temp table.


I hope this helps!


Login or Signup to post a comment