Sql Server table-valued parameter

Questions and answers regarding general SQL and backend databases

Sql Server table-valued parameter

Postby Janssenjos » Wed Oct 26, 2022 11:12 am

Hello,

Does anybody know if it is possible to use Sql Server table-valued parameters from within Servoy? https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-ver16
I have been looking through the documentation and at first glance I can't really find that it is possible.

Our specific use case is that we build up a custom sql query in code with parameters and use the DatabaseManager.getDataSetByQuery.
We have also looked into the rawSql plugin.

But in this use case we have some set based considerations, which can variate to larger sets. In which we could simplyfy our code if we can use table valued parameters.
Now we have a sql query with 10-to-60 parameters, which vary on each users settings. With use of a defined type, we could always have 1 parameters for the query. (Which also boosts reusability in sql server for query plans/caching: better performance)

Using table valued parameters seems the most obvious/simplest solution, hence we want to ask if it is possible first before looking for other solutions.

We are running the latest LTS (22.3.x)
Jos Janssen
Software Developer
Axerrio
http://www.axerrio.com
Janssenjos
 
Posts: 148
Joined: Thu Aug 13, 2009 3:55 pm
Location: Bergen op Zoom

Re: Sql Server table-valued parameter

Postby jjennings » Fri Mar 31, 2023 6:12 pm

Bump..

I need to call a stored proc that expects a table-valued parameter. I believe I need the same functionality as Jos is asking about; does this exist?

Ex:
DECLARE @ThisParmID dbo.tableTypeWithIds
INSERT INTO @ThisParmID (tableTypeWithIdsID)
jjennings
 
Posts: 5
Joined: Mon Jan 09, 2023 4:39 pm


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 8 guests