Thursday, March 8, 2012

Custom function with multiple variables

Hello,
I must say I am not too experienced in writing custom functions in
TSQL. Perhaps I am asking impossible
I have run into problems that the client platform is not able to
transmit the unicode characters properly, so I need a solution that
woul allow to call insert/update queries with ASCII charset only. The
current solution is that every unicode string literal is replaced with
a long sum of NCHAR(n) and string literals containing only ASCII chars.
That made me run into some wierd 'query optimisation' error.
I am now thinking maybe I could create a function where I could pass
all the unicode code values and string literals and what would
concatenate all them into one string. So, the question is -
1) Is it possible to create a function with variable number of
arguments
2) Can these arguments be of different types - mixed integer and string
3) Is TSQL strong enough to provide means of creating program that
loops over all the arguments and concatenates all of them into one
string, converting the int arguments uzing NCHAR() function.
Example:
MyFn(256,1000,'ABC',300,400,'1234')
returns ''ABC?' (The funny unicode chars replaced with ? for the
sake of example)
Thank you for any hints,
Pavils> 1) Is it possible to create a function with variable number of arguments ?
No. The number of arguments is defined when you create the function.
Even if you specify default values for some arguments, when you call
the UDF you must specify the DEFAULT keyword in the place of the values
for those arguments.

> 2) Can these arguments be of different types - mixed integer and string ?
No, but in SQL Server 2000 (or later) you can use the sql_variant
datatype, which can represent any other scalar datatype (except text,
ntext, image and timestamp).

> 3) Is TSQL strong enough to [...] ?
Maybe, but I don't think that would be a good idea.
You should think about another way of dealing with this issue.
Razvan

No comments:

Post a Comment