Dynamic SQL Parameters

[Eric1977]

I am working on a stored procedure via LabVIEW that looks at the parameter data type and determines what one to assign via the DB Tools Create Parametrized Query.vi

Looking at some past forum posts, I came across this one stating that a certain forum member uses a For Loop with a Case Selector to assign the data type, direction, and data type to assign. I thought it was pretty smart to do it this way, but I think it can go a bit farther.

For my scenario, I have a LOT of parameters (123 to be exact) to pass in. Looking at Stack Overflow and eventually asking Bing AI, I found a query that looked promising. You can query SQL Server - which I am using - to look up information about any object. Asking Bing AI if there was a way to extract parameter information from a stored procedure. This is what it returned.

SELECT
p.name AS ParameterName,
t.name AS DataType,
p.max_length AS MaxLength,
p.is_output AS IsOutput
FROM
sys.parameters AS p
JOIN
sys.types AS t ON p.user_type_id = t.user_type_id
WHERE
p.object_id = OBJECT_ID('YourStoredProcedureName');

And it works...well, sort of.

If you replace the fields above with an asterisk (*), you'll notice that there are two fields named has_default_value and default_value. If you're using this or just looking at wheat is returned, it will not show you what the values are or tell you it has one if you place a stored procedure name in the WHERE clause. Bummer. I figured it would show more info if you are using this with tables or other objects.

But, I don't think all is lost. I've created a VI that takes in all of this information and assigns the types based on what data type gets returned from the system tables. I think it's correct, but my issue is that I don't think that I've got the values right to pass in NULL values or, if it sees a date/time data type, what to pass in. Someone more familiar with SQL Server might be better off answering that.

I'd like to get some feedback from you or improvements. I'd rather not have 123 Set Parameter Value VIs. That would suck.

[aputman]

I'm not an SQL expert, but I think 123 parameters is too many for one procedure. Not that it can't be done, but should it?  Typically, you would want to normalize it better but it depends on your data. 

Won't you need some sort of case/loop structure to generate that parameter array?  You're converting all of the values to strings, storing them into an array, and then converting them all back to the original data type.  Seems like a lot of extra work to me as opposed to creating 123 "Set Parameters". I'm not sure why you even need to use the Set Parameter VI with the method I proposed in that post.  I bring the values over in a large cluster so the datatype remains unchanged. 

There is a function in the toolkit to return a dB Null value. (Advanced menu)

There is also a function to convert a Labview timestamp to a dB timestamp. (Utility menu)