Hi Rick,

I'm attempting to use wwSQL with an update statement that looks like this:

UPDATE MyTable SET myStatus='Processed', myDate=?ptDateSubmitted WHERE myID IN (?m.pcIDs);

pcIDs contains a comma delimited string of integer values (i.e. 123,124,125). When I run the Execute method, the update is failing with this error:

[SQL Server]Conversion failed when converting the varchar value '82,83' to data type int. [1526:245]

It will be trivial to just build a statement without using the ?var syntax. I'm just wondering if there is a way to make this work in that context.

TIA

Yeah that's not going to work. You're passing a string not two numbers. For this to work with ? syntax you need to pass each value as a number:

PRIVATE lnId1, lnId2  && make sure these can pass down into the actual SQL Execution code
lnId1 = 82
lnId2 = 83
UPDATE MyTable SET myStatus='Processed', myDate=?ptDateSubmitted WHERE myID IN (?lnId1, ?lnId2)

And FWIW, what you're using here is plain SQL Passthrough syntax.

Thanks, Rick.

I guess it would be equally trivial to build it out for # values using ? expansion I don't think it's worth the effort and will stick with building the list via textmerge.

You are so stuck in the FoxPro way of doing things 😄

You can just use build the string with the values either using & macro expression or string concatenation.

In this case using a manual string is OK because it's numbers and they are likely not coming from user input, right?

Manual string building in SQL is a bad idea in 90% of the cases and especially if working with values coming from user input.

+++ Rick ---

You say that like it's a bad thing? 😉

As you inferred the source for this list of values is not coming from direct user input. I could have written the whole thing in T-SQL using string_split to separate the string values into unique integers but why make this any more complicated than necessary. At the end of the day I just needed a comma separated list of integer values to stuff into the IN clause of a very simple SQL update statement. My question was more of a confirmation that there was no Rick Strahl magic trick in the wwSQL class that would do the work for me in the preferred way. Thanks for playing along, as always.

No no magic. Just SQL (which is its own kinda magic) 😄

What you have here is a special case in that you are providing a set, and there's no way to represent a set via parameters other than actually creating the values as parameters. Other than building the string I don't see an alternative.

+++ Rick ---

It is, indeed. 😃 Getting my head wrapped around all the goodies added to T-SQL has been part of the fun in recent years.