DCT parameterized query - strange behavior

[Cranky]

I'm using the database connectivity toolkit to build a parameterized query, but it's not working as expected. I have four parameters, one of which is set by an array of values. When each array value is passed singly (1 element array), the query works. When passing two values, the order determines if both record sets are returned. I.e. passing "Temperature" before "Humidity" returns both of the expected recordsets, but reversing the order causes the second set to return empty. Has anyone else seen this kind of behavior before?

I'm not sure how to do a parameterized query from SQL Server Management Studio, but that's next on my list of troubleshooting.

Details:

LabVIEW Full 2011 SP1 (11.0.1f2)

DCT (11.0.0)

SQL Server Express 2012

[aputman]

Your parameters array is empty.  It must have the same number of parameters as your query.

[Cranky]

It's definitely populated in my copy, otherwise it would throw an error for any/all of the set parameter.vi calls. When this behavior happens, no errors are thrown and the query seems to execute successfully.

[aputman]

True.  I see nothing that would cause this behavior. 

Your recordset data 2 control is a 3 dimensional array.  are you sure that you aren't viewing the wrong indexes?

[Cranky]

I've got a probe on the variant array output of the Fetch Recordset to check the raw output. The 3 dimensional array shows the correct data when both sets return correctly, but that's not where I'm monitoring the results.

I'm still trying to find some info on executing a parameterized query in Management Studio, but there doesn't seem to be much in terms of example code.

[aputman · Accepted solution]

This should get you really close.  Just wire your inputs into the right parameter and create the stored procedure in your database.

[Cranky]

Interesting. I'm still pretty new to SQL, so I hadn't thought of stored procedures yet. I'll give that a whack and see how it works.

Thanks for your feedback!

[Cranky]

Looks like a stored procedure is the way to go. The only snag I hit was the missing "END" in  your SQL text.

This doesn't fix the root cause, but does provide a workaround, so I'll mark it as the solution. Thanks again!