How can I use a Workflow Variable as a parameter in Execute SQL command


Badge +4

I have a workflow where I am using an Execute SQL action to call a stored procedure that requires two parameters. The first parameter I can type in but the second parameter needs to come from a Workflow variable.  The workflow action receives the Workflow variable but I am unable to figure out how to insert a single quote before and after the parameter. Currently the workflow errors out with the error 'Invalid Column Name'. I have found many posts where people have been able to get this to work for a SQL Request but I can not seem to find a solution for the Execute SQL action.

Here is my SQL Query.

SELECT [dbo].[GetEmailStringForGroup] (
'Purchasing',{WorkflowVariable:wrkLocationAbbr})

I have tried to use double quotes and single quotes but every time I try to do so it completely wipes out the value of the variable and supplies either '' or "" to the SQL stored procedure.

So how can I as a single quote before and after the variable so that the SQL query will execute correctly?

Thanks so much.

Randy


7 replies

Userlevel 5
Badge +14

how/where exactly you've tried to place quotes?

I'd say following should work

SELECT [dbo].[GetEmailStringForGroup] ('Purchasing','{WorkflowVariable:wrkLocationAbbr}')

do you have by any chance a quote character(s) within variable value? if so remove or escape it.

Badge +4

Marian thank you so much for the reply.

Yes, I have tried your exact example 

SELECT [dbo].[GetEmailStringForGroup] ('Purchasing','{WorkflowVariable:wrkLocationAbbr}')

And I have tried

SELECT [dbo].[GetEmailStringForGroup] ('Purchasing',"{WorkflowVariable:wrkLocationAbbr}")

When I add the single quotes it appears to be passing {WorkflowVariable:wrkLocationAbbr} as the string rather than the value of the workflow variable because the results are coming back as empty instead of returning a list of emails. The double quotes just fails all together as expected.

This code works 

SELECT [dbo].[GetEmailStringForGroup] ('Purchasing','TN')

but I have multiple locations and I want to have it use the current location in the code. I really do not want to have to make a hideous switch statement with multiple Execute SQL commands.

Thanks,
Randy

Userlevel 5
Badge +14

have you checked what I've asked above?

do you have by any chance a quote character(s) within variable value? if so remove or escape it.

When I add the single quotes it appears to be passing {WorkflowVariable:wrkLocationAbbr} as the string rather than the value of the workflow variable because the results are coming back as empty

are you sure about that or you just guess?

do you have a chance to check at DB side what exactly comes into procedure?

(eg. let the procedure write input parameters toa log or table)

have you checked exact content of workflow variable just before it is passed into DB call?

write the variable value to workflow history log and/or send it to yourself in mail notification (as plaintext!) and check it contains expected value or doesn't have typos. make sure it doesn't contain leading or trailing spaces, non printable characters (eg. line feed, non-breakable space, unicode characters...) or some 'sensitive' characters (slashes, quotes, (semi-)colons...) that might be misinterpreted.

Badge +4

Marian,

Thanks again for your reply.  To answer your question, yes if I put the single quotes around the variable as I demonstrated it passes the text '{WorkflowVariable:wrkLocationAbbr}' as a string. I've tested this because I put a log event in to test the return value and I get the same results as I did in the test panel...a blank result meaning I supplied a location that didn't exist. Personally I think it is a bug in the Execute SQL action. 

I've gotten it working with work arounds several ways.

Work Around One.

This is the work around I used because it is the easiest to come back to in a year and figure out what I did.
Marian per your suggestion, on the Nintex form I put a calculated field that saves the location in another list field and puts the single quotes around the value. I am saving the location to the list field called SqlLocation. If the location is TN then is saving 'TN' to SqlLocation. Then in my workflow I set wrkLocationAbbr = SqlLocation. Then this command works.

SELECT [dbo].[GetEmailStringForGroup] ('Purchasing',{WorkflowVariable:wrkLocationAbbr})

But it shouldn't be necessary to do this! One reason why I say that there is a bug in the Execute SQL action.

Work Around Two.

I created a wrkSqlCommand workflow variable which I saved the entire SQL Command as a string into the workflow variable. I used a String Builder action to build the variable. In the Action Text I pasted in exactly what I was trying to into the Execute SQL....

SELECT [dbo].[GetEmailStringForGroup] ('Purchasing','{WorkflowVariable:wrkLocationAbbr}')

Notice that it has the single quotes and it works. I stored the result into wrkSqlCommand and then put in a Log action to see the result and it resulted in...

SELECT [dbo].[GetEmailStringForGroup] ('Purchasing','TN')

This is exactly what I need. Except the Execute SQL should be able to parse the text exactly like the String Builder action does. So again I say that there is a bug in the Execute SQL action.

Work Around Three.

A co-worker had a stroke of genius and figured out a way to over come the 'invalid column' error by giving it what it wants...a valid column!

SELECT [dbo].[GetEmailStringForGroup] ('Purchasing',{WorkflowVariable:wrkLocationAbbr}

from (select 'TN' as TN, 'AZ' as AZ, 'AR' as AR, 'VA' as VA) a

This supplies the columns and replaces them with the properly formatted string. Unfortunately you have to know all possible locations for it to work. But again shouldn't be necessary! Though I thought it was a very smart work around!

Thanks,
Randy

Userlevel 5
Badge +14

yeah, that really looks like a bug.

it looks like nintex do not recognize correctly whether quote sign belongs to SQL statement or it wraps a supplied string from workflow.

can you try following whether it works any better?

SELECT [dbo].[GetEmailStringForGroup]('Purchasing',{TextStart}'{TextEnd}{WorkflowVariable:wrkLocationAbbr}{TextStart}'{TextEnd})
Badge +4

Yes the {TextStart}'{TextEnd} actually works! I think I like that even better because in my mind it is more clear should I come back in a year and try to remember what I did.

Badge +4

I've discovered an interesting fact about this issue that I would like to report on so that no one is steered wrong from reading this post....

The command that I am calling in this post is calling a Scalar-valued Function. It requires that I pass it parameters just like any function.

SELECT [dbo].[NameOfFunction] (<parameter1> ,<parameter2>)

In another workflow I am using an Execute SQL action that calls a store procedure and it works perfectly.

DECLARE @return_value int

EXEC @return_value = [dbo].[NameOfStoreProcedure]

@SPID = {ItemProperty:ID},
@Title = N'{ItemProperty:Title}',

@Status = N'{ItemProperty:Status}',

@BitVariable = WorkflowVariable:VariableName} 

SELECT 'Return Value' = @return_value

When I call the store procedure it processes everything perfectly (namely the single quotes). So the truth to this matter is that the Execute SQL action does not know how to handle the text in between the parenthesis when calling a function. The odd thing about this is that I even tried to make a variable.

DECLARE @StringVariable varchar(100) = '{ItemProperty:stringVariable}';

SELECT [dbo].[NameOfFunction] (<parameter1> , @StringVariable)

and this didn't work either. So Nintex...maybe you need to update workflow to be SQL Function aware?

Again, I very much appreciate Marain's workaround of using {TextStart}'{TextEnd}

Reply