function with a parameter of type object.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

function with a parameter of type object.

Author

Topic

mathmax
Yak Posting Veteran

95 Posts

Posted - 2008-06-10 : 16:32:28

Hello,

I would like to create a function that accepts a value of any type as parameter. I mean, it could receive a value of type int, datetime, char,...
In .net, I would use the type "object". Is there an equivalent for SQL server function ?

Regards,

mathmax

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-10 : 16:34:41

sql_variant?

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-10 : 16:35:28

This is such a bad idea. Why would you want to do this?

You'll need to use varchar data type and then convert as necessary in your code. Yikes!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

mathmax
Yak Posting Veteran

95 Posts

Posted - 2008-06-10 : 16:46:42

I'm currently migrating an old Clipper + DBASEIV program to make it run on SQL server. In this context, I have to convert Clipper expressions to SQL server expressions. Some Clipper functions, such as iif(,,) don't exist in SQL server. I would like to write them on the database side.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-10 : 16:48:17

I don't see how that applies to your original question.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

mathmax
Yak Posting Veteran

95 Posts

Posted - 2008-06-10 : 16:56:46

I write this :

create function iif(@Condition bit, @IfTrue sql_variant, @IfFalse sql_variant)
returns sql_variant
as
begin
return case when @Condition = 1 then @IfTrue else @IfFalse end
end
go

the second and third parameters and the return value can be of any type. That do the trick except the fact that this call will generate an error:

dbo.iif(1=1, 'aa', 'bb')

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.

How to make it work with a logical expression as first parameter ?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-06-10 : 17:00:44

It won't work that way in T-SQL. You can't cast a boolean expression to a BIT value without using CASE, so you gain nothing by trying to write a function like this over just replacing your IIF() functions with CASE expressions completely.

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-10 : 17:04:12

You can't.
Unless you use some kind of dynamic SQL, which is not allowed in functions.

E 12°55'05.25"
N 56°04'39.16"

mathmax
Yak Posting Veteran

95 Posts

Posted - 2008-06-10 : 17:09:59

Ok, so there is no way to store a boolean expression in a variable in T-SQL ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-10 : 17:21:24

Yes. But you can't evaluate it in a UDF (User Defined Function).

E 12°55'05.25"
N 56°04'39.16"

mathmax
Yak Posting Veteran

95 Posts

Posted - 2008-06-10 : 22:11:06

Where can I evaluate it?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-06-10 : 22:31:24

quote:
Originally posted by mathmax

Where can I evaluate it?

Using CASE. Why don't you just replace your IIF() usage with CASE expressions? You've already shown that you know how to use CASE in your UDF, so just use it.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources