how to use 3 returns from a function? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

how to use 3 returns from a function?

Author

Topic

allan8964
Posting Yak Master

249 Posts

Posted - 2011-12-17 : 19:38:10

Hi there,
I have a function which returns 1 or 3 int results and these integers will be used in where clause:

GetValues(arg1, arg2)
...
Returns Int1, Int2 and Int3 which will be used in

select * from table
where Type=Int1 or Type=Int2 or Type=Int3

I have function working but how can I distribute 3 results to the where clause? Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-17 : 23:57:27

Is there any relation between the input parameters of your function and the table's data? Is it a table-valued function or a scalar function?

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

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-18 : 01:19:45

Seems like a table valued function to me as it suggests it is returning three integer values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-12-18 : 12:37:46

If your function is table valued function, then using join (better performance).

If your function is scalar, returning string like : 'Int1, Int2 and Int3', surround the int with special charter like '_' then using charindex in the where clause.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-18 : 23:43:01

from performance perspective I think its much better to return it as table valued if you want to return multiple values rather than making it delimited list and using a further parsing logic on it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

allan8964
Posting Yak Master

249 Posts

Posted - 2011-12-19 : 11:30:16

tkizer, visakh16 and namman, thank you so much for the replies.

I think the function is a scalar one. It actually separate some args, which will be used in where, by using some sql functions like charIndex(), etc. For example, the original string is "202-305-819" and I need to extract from it as three single strings as '202', '305', and '819', and later I need to put them into where clause as

Where type='202' or type='305' or type='819'

So for this scenario what is the best practice? Well, at least I can try namman's suggestion. Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 11:39:24

quote:
Originally posted by allan8964

tkizer, visakh16 and namman, thank you so much for the replies.

I think the function is a scalar one. It actually separate some args, which will be used in where, by using some sql functions like charIndex(), etc. For example, the original string is "202-305-819" and I need to extract from it as three single strings as '202', '305', and '819', and later I need to put them into where clause as

Where type='202' or type='305' or type='819'

So for this scenario what is the best practice? Well, at least I can try namman's suggestion. Thanks again.

if thats case why should you split them out?
wont this be enough?


Where '-' + yourUDFReturnValue + '-' LIKE '%-' + type + '-%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

allan8964
Posting Yak Master

249 Posts

Posted - 2011-12-19 : 12:16:50

thanks visakh16 but I am not following ... I have 3 returned vars in the function. Do you mean I need to put them into one with dash signs and then put them into where clause? Could you be little bit more specific?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 22:29:19

hmm? so what will this return?

SELECT dbo.yourfunction(params...)

one value containing 3 integers delimited or three separate values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-19 : 22:41:49

[code]
select *
from table
where Type in
(
select Data
from dbo.fnParseList('-', '202-305-819')
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

allan8964
Posting Yak Master

249 Posts

Posted - 2011-12-20 : 14:23:36

The function returns three separated values.
For khtan's solution I think it works for the sceanrio of

where (type=202 OR type=305 OR type=819)...

my situation is that I need

where type=202 OR type<>305 AND type>819 ...

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-20 : 14:32:54

Your original post states:

quote:

where Type=Int1 or Type=Int2 or Type=Int3

It's important that you specify exactly what you want so that we don't waste time coming up with solutions that are not relevant to your problem.

Are there any other situations that would be different than your new requirement? We need to get the full picture here before proceeding with new solutions.

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

Subscribe to my blog

allan8964
Posting Yak Master

249 Posts

Posted - 2011-12-20 : 15:19:16

I think I can use the function this way:

GetValues1(args...) returns 202
GetValues2(args...) returns 305
GetValues3(args...) returns 819

yes, it works. Any better idea?
By the way, please dont't answer it if you think it's a waste of time!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-21 : 12:21:25

quote:
Originally posted by allan8964

I think I can use the function this way:

GetValues1(args...) returns 202
GetValues2(args...) returns 305
GetValues3(args...) returns 819

yes, it works. Any better idea?
By the way, please dont't answer it if you think it's a waste of time!!!

its not actually but you're making it like that yourself..
reasons are below
1. not giving clear picture of whats the problem and whats the solution you're looking at
2. not providing some sample data to make us clear what your problem is and what all cases can come

please see the link below and understand how to post a question for database
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Had you posted the question clearly and with sufficient detail, I'm sure by this time you would have got solution from someone!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

allan8964
Posting Yak Master

249 Posts

Posted - 2011-12-21 : 23:50:02

Since you don't think it's a waste of time I want to say something as well.

1) My real codes are more complex than what I provided here so I can only give a simple sample to show people what I need. If I submit all of the codes to make it a "clear" question I believe you may ask me to "see the link below ..." for brevity.

2) When people submit questions, in order to make point they may simplify them, and sometimes something could be missing, like you missed an apostrophe in "its not actually but you're making it like that yourself.." But it won't make people misunderstand you. Right? (Don't worry, lot more people there miss that sign.)

3) In my case, using OR or AND in the where clause doesn't affect the nature of the questions. At most it's an apostrophe issue. The question here is not OR or AND used as conditions. The question here is can we return multiple values in one sql function. (Yes or No?)

I think in a free forum no one can enforce anyone else to waste time.
Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-22 : 00:45:44

quote:
3) In my case, using OR or AND in the where clause doesn't affect the nature of the questions. At most it's an apostrophe issue. The question here is not OR or AND used as conditions. The question here is can we return multiple values in one sql function. (Yes or No?)

It does makes a different.

for "where (type=202 OR type=305 OR type=819)", i would suggest a solution like

select * 
from   table
where  Type in
       (
           select Data
           from   dbo.fnParseList('-', '202-305-819')
       )

for "where type=202 OR type<>305 AND type>819", i would suggest another function fnParseString() from the same link


WHERE type =  dbo.fnParseString(-1, '-', '202-305-819')
OR    type <> dbo.fnParseString(-2, '-', '202-305-819')
AND   type >  dbo.fnParseString(-3, '-', '202-305-819')

The basic might be still parsing or splitting of string, but depending on requirement and how the result is being utilize, it will required different way of doing it.

Also, you might be also use fnParseString for the 1st requirement but there are some limitation to it. That's why i didn't suggest that in the first place.

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-22 : 01:37:55

quote:
Originally posted by allan8964

Since you don't think it's a waste of time I want to say something as well.

1) My real codes are more complex than what I provided here so I can only give a simple sample to show people what I need. If I submit all of the codes to make it a "clear" question I believe you may ask me to "see the link below ..." for brevity.

2) When people submit questions, in order to make point they may simplify them, and sometimes something could be missing, like you missed an apostrophe in "its not actually but you're making it like that yourself.." But it won't make people misunderstand you. Right? (Don't worry, lot more people there miss that sign.)

3) In my case, using OR or AND in the where clause doesn't affect the nature of the questions. At most it's an apostrophe issue. The question here is not OR or AND used as conditions. The question here is can we return multiple values in one sql function. (Yes or No?)

I think in a free forum no one can enforce anyone else to waste time.
Thanks.

Ok..perfectly fine with that...but one thing to keep in mind along with that is to give a clear picture to someone trying to spend sometime to help you out. Unless you do that, its impossible for someone not knowing system and not understanding your rules to come up with accurate solutions. Hence they've to do some kind of guess work which results in lot of time and effort wasted both from your as well as from helper perspective as there will be lot of iterations involved.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources