How to pass more than one parameters in Run SQL Query

Permanently deleted user

23. August 2019 19:50

0

• 

  Permanently deleted user

  23. August 2019 20:10

  Hello,

   

  I am using sever-side "run SQL query" activity in WF5 to query in MS SQL Server database, when I tried to query a table using only one parameter, such as

   

  Command Text: SELECT * FROM myTable where ID =@IDInput 

   

  and Parameter: ={'IDInput':'24112'}, it worked with correct result.

   

  However, when I tried to pass more than one parameters, such as :

   

  Command Text: SELECT * FROM NOTIFY_ALL where ID =@IDInput or Add1=@Add1

  and Parameter : ={'IDInput':'24112', 'Add1':'Add11'}, it returned me wrong values, it looked to return the result for the second part of query.

   

  Actually, I would like to pass a set of IDs into "IN" where clause, Command Text like : SELECT * FROM myTable where ID in ( @IDInput )

   

  and Parameter: ={'IDInput':"'24112','24112'"}, it returned me wrong result, empty result always.

   

  Just wonder if anyone has made it working? Any input is highly appreciated!

   

  0
• 

  Claire Inbody

  5. November 2019 16:21

  Did you figure out the parameter issue? I am trying to pass 2 parameters into a report and it fails.

  0
• 

  Permanently deleted user

  7. Januar 2020 09:58

  Anybody ever figure this one out? I'm experiencing the same issue.

   

  James

  0
• 

  Permanently deleted user

  8. Januar 2020 17:57

  Passing in 2 parameters works on my end.

  Eg.

  Command Text:

  SELECT NAME, OBJECTID_1 FROM amanda.dbo.CITIES WHERE NAME=@name or CSD_NAME=@name2

  Parameters:

  ={ "name": "Calgary", "name2": "Greenwood" }

   

  Passing in one parameter that contains a list to use on for a where clause with an IN operator is difficult to do, and I would recommend testing your logic directly in SQL Server Management Studio (or your DB/SQL program of choice) before testing it in workflow.

   

  My working example above using SQL Server would be written like this:

  DECLARE @name varchar(25); DECLARE @name2 varchar(25); SET @name = 'Calgary'; SET @name2 = 'Greenwood'; SELECT NAME, OBJECTID_1 FROM amanda.dbo.CITIES WHERE NAME=@name or CSD_NAME=@name2; GO

  The format Hua described would probably convert to something like this, which does not work for SQL Server (note-single quotes are escaped by doubling them):

  DECLARE @name varchar(50); SET @name = '''Calgary'',''Greenwood'''; SELECT NAME, OBJECTID_1 FROM amanda.dbo.CITIES WHERE NAME IN (@name); GO

  Running a web search for "sql server parameterized in clause" will probably get you some resources in the right direction for this.

  There's also the option of using the Run SQL Query Unsafe activity so that you can use an expression for the "command text" and skip using parameters entirely, but you'd have to accept the risks of doing it that way.

  0
• 

  Permanently deleted user

  8. August 2020 23:20

  The following method has worked for me:

   

  assuming you use Get Workflow Inputs activity to get the dynamic value list, $getWorkflowInputs1.inputs["namelist"]

   

  you first use Create Value activity to create a strSQL variable, and then assign value using an expression similar to : = "SELECT * FROM tablename WHERE Name in (" + $getWorkflowInputs1.inputs["namelist"] + ")"

   

  then for the Run SQL Query activity, put EXECUTE (@sql) in the Command Text, and put ={"sql" : $strSQL.result} in the Parameters

   

  Give it a try and hope it works for you, too!

  0

Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.