Passing parameters to query without TVFs

Question

Post reply

Passing parameters to query without TVFs

seanachim@hotmail.com

SSC Enthusiast

Points: 164
More actions
March 27, 2023 at 12:00 am

#4162925

Comments posted to this topic are about the item Passing parameters to query without TVFs
March 27, 2023 at 5:23 am

This was removed by the editor as SPAM

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

fregatepllada SSCommitted Points: 1700 More actions · Answer 1

SELECT * FROM is not a good practice.
You could wrap multiple parameters into a single XML/JSON document and pass it as a SINGLE parameter into StoredProcedure

Jonathan AC Roberts SSCoach Points: 18256 More actions · Answer 2

Just wondering what is the point of @Period in function fn_TableByPeriod?

Michael L John One Orange Chip Points: 27038 More actions · Answer 3

fregatepllada wrote:

SELECT * FROM is not a good practice.

Never?

fregatepllada wrote:

You could wrap multiple parameters into a single XML/JSON document and pass it as a SINGLE parameter into StoredProcedure

Using sp_xml_preparedocument? No thanks. When that is called, it takes up 1/8 of the memory available to SQL. JSON is far better, but I'll take any number of other methods over both of these.

The answer is "It depends".

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Robert-378556 SSCertifiable Points: 5917 More actions · Answer 4

You might be mixing apple and oranges. Presented case is not "passing parameters", but using a configurable global variable.

A good use case for a view, but very bad for a function. Views/functions/procedures have their uses, common use is to create a reusable object that encapsulates a complexity that would otherwise be copied into several places.

Suppose you need something like this:

select P.period, t.* from periods P cross apply fn_MyTablesByPeriod(P.period) t

You can join periods to v_TableByPeriod as well, but your modified function as well as the view would always the same data regardless to parameter or joined value.

BTW, "select top 1 ...." without the "order by" is a bad practice - someone creates a clustered index, the "natural" order changes and the code breaks. Software is always evolving, so write robust code from the start, never assume things.

Ron Hinds SSC-Addicted Points: 412 More actions · Answer 5

It seems that the author made a mistake in the last example presented, because in the text he is talking about a view, but the example given is for the TVF.

This reply was modified 1 year ago by Ron Hinds. Reason: I didn't quote the person I was replying to, so I edited to make it more generic

seanachim@hotmail.com SSC Enthusiast Points: 164 More actions · Answer 6

Thanks everyone for the comments.

I've avoided writing articles for several years because I always feel that it takes a lot to write something simple - and often enough it is the simple stuff that trips me up.

My first pass on this article was rejected because I didn't apply my mind sufficiently to the readability, which was an education entirely on its own.

I've learnt even more from the comments that have now been made. In particular, our profession expects us to be pedantic about our code. Never write a piece of code as an example in any form of shortcode because you are expected to write robust code.

Sometimes, however, I find that if we are trying to say something like "get some data from a given table or view" it is easier to say

Select * from xxx

I get that this may be distracting for some of us because it immediately draws comments about never selecting all of the columns in a query and never write a query without a filter.

I guess it never helps to start a loose conversation about the principle of the thing without investing the time and energy that ensures that the point you want to discuss is well thought through and thoroughly motivated.

The problem is that we are all busy doing the stuff that we want to talk about and don't always have the time to make well a documented case for a given technique.

So, thanks for the insights, I will try to keep my code more robust when posting another thought when I get a chance again.

Perhaps someone could comment on when they feel this technique would be of help? It would be great to know that someone had an idea inspired from this technique that I could learn from or someone else has already burnt their fingers and can share with me that story so that I can protect myself as well.

seanachim@hotmail.com SSC Enthusiast Points: 164 More actions · Answer 7

@jonathan-2 AC Roberts

What is it that you are wondering about in this regard?

Ron Hinds SSC-Addicted Points: 412 More actions · Answer 8

seanachim@hotmail.com wrote:

@Jonathan AC Roberts
What is it that you are wondering about in this regard?

That is the comment I was trying to reply to. In the last example, you are talking about the view, but the code is for the TVF. I believe that's what he was referring to.

Jonathan AC Roberts SSCoach Points: 18256 More actions · Answer 9

Jonathan AC Roberts wrote:

Just wondering what is the point of @Period in function fn_TableByPeriod?

seanachim@hotmail.com wrote:

@Jonathan AC Roberts
What is it that you are wondering about in this regard?

Alter FUNCTION fn_TableByPeriod(
@Period int
)
AS
RETURN
(SELECT * FROM MyTables WHERE Period=(SELECT TOP 1 Period FROM dw.FilterPeriod))

Michael L John One Orange Chip Points: 27038 More actions · Answer 10

seanachim@hotmail.com wrote:

Thanks everyone for the comments.
I've avoided writing articles for several years because I always feel that it takes a lot to write something simple - and often enough it is the simple stuff that trips me up.
My first pass on this article was rejected because I didn't apply my mind sufficiently to the readability, which was an education entirely on its own.
I've learnt even more from the comments that have now been made. In particular, our profession expects us to be pedantic about our code. Never write a piece of code as an example in any form of shortcode because you are expected to write robust code.
Sometimes, however, I find that if we are trying to say something like "get some data from a given table or view" it is easier to say
Select * from xxx
I get that this may be distracting for some of us because it immediately draws comments about never selecting all of the columns in a query and never write a query without a filter.
I guess it never helps to start a loose conversation about the principle of the thing without investing the time and energy that ensures that the point you want to discuss is well thought through and thoroughly motivated.
The problem is that we are all busy doing the stuff that we want to talk about and don't always have the time to make well a documented case for a given technique.
So, thanks for the insights, I will try to keep my code more robust when posting another thought when I get a chance again.
Perhaps someone could comment on when they feel this technique would be of help? It would be great to know that someone had an idea inspired from this technique that I could learn from or someone else has already burnt their fingers and can share with me that story so that I can protect myself as well.

Don't beat yourself up too bad. The article was great, and there was not anything "wrong "with the code you posted. It conveyed the concept perfectly. Most readers would understand that the code is demo code, and it would need to be cleaned up and adapted prior to using something like this in a production environment.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Robert-378556 SSCertifiable Points: 5917 More actions · Answer 11

Agreed. Criticism is for you and readers to learn something, don't let it discourage you from publishing more great articles. Comments mean that readers got interested, better than no feedback at all.

Btw, I wrote that it's good use case for a view (I use this concept myself), but not for a function. A function must not have a parameter that is ignored and must not depend on "global variable".

This reply was modified 1 year ago by Robert-378556.

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 12

My questions are that if granting permissions and end user proficiency are a problem how is the solution of creating a filter table better? That still requires the user to have extra permissions to that table to insert into it and it requires them to run a separate insert statement prior to querying. Which if one of your use cases is trying to pull the data into excel would be harder than just running a TVF or SP.