Pass an unknown number of values to a function

Question

Pass an unknown number of values to a function

BGM

SSC-Addicted

Points: 421
More actions
May 24, 2012 at 7:33 am

#261903

Hello,
I have what i hope is a relatively simple question, that i will keep deliberately non-specific 😉
Basically, there are some built in functions within SQL server that allow an unknown number of parameters. The most obvious one of these that i can think of is Checksum()
How do these work? Can i look 'inside' of Checksum() somehow?
Ultimately what i want to be able to do say:
select myFunction(field1, field2, field3, fieldn) from myTable
and have the flexibility to have myFunction do whatever i choose 😉
I am thinking a CLR might be required here... thanks in advance!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

BGM (5/24/2012)
Hello,
I have what i hope is a relatively simple question, that i will keep deliberately non-specific 😉
Basically, there are some built in functions within SQL server that allow an unknown number of parameters. The most obvious one of these that i can think of is Checksum()
How do these work? Can i look 'inside' of Checksum() somehow?
Ultimately what i want to be able to do say:
select myFunction(field1, field2, field3, fieldn) from myTable
and have the flexibility to have myFunction do whatever i choose 😉
I am thinking a CLR might be required here... thanks in advance!

Simple:

No - you cannot look inside of Checksum() or Coalesce() - they are internal functions

No - you cannot have SQL user-defined function with unknown number of parameters (param array)

No - you cannot have CLR function with unknown number of parameters (param array)

The only way to pass "unkwon number" of parameters is to use some-separated string or input parameter of TABLE type : http://blog.sqlauthority.com/2008/08/31/sql-server-table-valued-parameters-in-sql-server-2008/

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

BGM SSC-Addicted Points: 421 More actions · Answer 2

err.. thank you for being so terse? :hehe:

I guess the obvious follow up question is how the crap does checksum() work then? 😉

Cheers!

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

...
I guess the obvious follow up question is how the crap does checksum() work then? 😉
...

You will never sure exactly, as you will need to look into SQL Server source code. I'm afraid MS no longer supply it together with software and stopped it to be downloadable from their Download centre...:hehe:

checksum() is not CLR and is not udf. It's part of SQL Server code base.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

BGM SSC-Addicted Points: 421 More actions · Answer 4

shame 🙁

thanks for the quick responses, much appreciated.

seth delconte SSCertifiable Points: 6388 More actions · Answer 5

BGM (5/24/2012)
err.. thank you for being so terse? :hehe:
I guess the obvious follow up question is how the crap does checksum() work then? 😉
Cheers!

I think this might give you some ideas to do what you want:

http://stackoverflow.com/questions/2894917/creating-a-clr-udf-with-variable-number-of-parameters

Don't worry about the 'terseness' - I think a lot of members here wear really tight underwear! 😉

_________________________________
seth delconte
http://sqlkeys.com