Hi, i hope i'm doing this question justice. A peer of mine likes the idea of using user defined table types. I think mostly because he doesnt want to pass 84 data values representing his "row" to either procs or functions 2 or 3 levels deep. He also likes the idea of inserting into tables the returned tables from those nested procs or functions.
my understanding is that sql blocked more than one insert like the latter in nested calls like this. But he felt like the forums he looked at were suggesting funcs instead of procs. The problem there is that when he returns tables from those nested calls he would also like to return one or two statuses as well.
so i believe the question is "can you return both scalars and a table from a function to the caller?".
You can do that from a stored procedure, but I am pretty sure you can't from a function.
And to be honest, I'm not even sure how you'd do that in a query. A function is used in a SELECT statement- such as SELECT CAST(getdate() AS DATE) <-- here CAST is a function and getdate() is a function. How would you use a function to return multiple types (scalar and a table) from 1 function call? how would you write your query to use it?
Stored procedures have no problem doing this though as you can have return values (output variables) AND return a table (or 2 or 3 or 4 or ...).
I also don't see how a function is going to solve his problem. Whatever parameters are needed for the SP will be needed for the function as well. Functions don't magically generate parameters for you...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 1, 2024 at 5:21 pm
This was removed by the editor as SPAM
May 1, 2024 at 5:23 pm
This was removed by the editor as SPAM
May 1, 2024 at 5:38 pm
This was removed by the editor as SPAM
May 2, 2024 at 10:52 am
Thx Brian, like you we believe that a func can return only 1 value or only one resultset.
also a proc cant return a table variable.
so are you suggesting that the proc do both? and then if the last statement of the proc is a select, the caller can tap into both the output params and the resultset? Or like i've seen in the past the proc return parallel datasets (one with the output params) where the caller knows the name of the datsaets?
Then i think his remaining challenge is that block where sql doesnt like more than 1 insert from nested selects. I told him if that remains an issue he might want to consider xml or temp tables or an orchestration proc that calls these procs sequentially instead of nesting them .
May 2, 2024 at 3:31 pm
Admittedly your description is a bit vague, but it sounds like you are trying to force SQL into an object-oriented paradigm. For instance you mention "84 data values representing his 'row'". Talking about "rows" instead of "columns" is often a red flag when it comes to SQL coding.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply