Calling Functions

Dear Everybody,

Hi again!

Supposing I have an SQL Server Function named Fxn1 which returns a VARCHAR(6) value. I want to use this returned value in another function named Fxn2 but Fxn2 accepts a parameter of type INT. Fxn2 itself returns an INT value. This has to be achieved in a single line.

The following line of code will not work in SQL Server. How should I achieve this?

declare @.Res INT
EXEC @.Res = Fxn2 (CONVERT( INT, Fxn1 ))

Thanx in advance!Just curioius, but have you tried talking to the TA or your instructor? Its really important that you talk to them when you are stumped on homework, they need the feedback as much as you need the help finding the solution.

-PatP|||No thx for the suggestion. :rolleyes:

I always thought that this forum had people who cud come up with suggestions which were technical in nature!|||It does, but we still won't do your homework!

-PatP|||First of all stop calling this homework. You cannot pass judgements like that. To me it was a genuine problem that's why I referred it to this forum. Might be not for u.

If u r not interested, gr8. But keep ur thoughts to urself.|||"This has to be achieved in a single line."?

Are you running out of lines on your computer?

You may just need to create a single function that combines the logic of the two you have.|||I don't have any problem giving legitimate help (read a few thousand of my posts if you'd like to confirm that), but based on the attitude that you've displayed and the grammer and spelling that you've used (or more accurately failed to use), you appear to me to be a rather rude kid that is sqawking because they got caught trying to get someone to do their homework. If I've misinterpreted something, I'm sorry, but that is how you appear to me at the moment.

Based on the way you presented the question in your original posting, it reads like homework. Are you saying that it isn't homework? If not, can you give us a little background in what you are trying to do, and more importantly why it needs to be accomplished in a single line?

-PatP|||Are you running out of lines on your computer?I just hate it when that happens, don't you?

-PatP|||What error are you getting when you execute the statement? Obviously
EXEC @.Res = Fxn2 (CONVERT( INT, Fxn1 ))
...will error out if Fxn1 returns a string that cannot be converted to an INT.
Also, post the actual statement you are sending.|||Dear blindman (although I hate to use that term to address u),

First of all thx for ur concern & help. Can I show the audacity to ask your real name?

Second of all, lets move to my actual problem..

I have several procedures and functions in Oracle which I have to convert to SQL Server syntax. The Fxn1 and Fxn2 names that I had used was just to present the problem to the forum in a simple manner.
In one of the functions named CREATE_TRIPS_SCHED, a SELECT statement has been included (the result of which has been used in another query) which is as follows :

SELECT 'P'||T.T_ID, T.S_TIME, T.END_TIME,
NVL(T.DESIRED_TIME, MIN_TO_AMPM( TO_MINUTES(TO_NUMBER(AMPM2FULLHRS(T.EST_TIME))))),
T.DISP, DECODE(T.WHEEL_CHAIR,'Y',5,2)
FROM DISPATCH_TRIPS T
WHERE T.R_NUM LIKE 'X%'

In the above statement, MIN_TO_AMPM, TO_MINUTES and AMPM2FULLHRS are all UDFs, out of which MIN_TO_AMPM and TO_MINUTES fxns return INTEGER values and AMPM2FULLHRS returns a VARCHAR value.

The conversion for SQL Server that I had done is as under :

SELECT 'P' + T.T_ID, T.S_TIME, T.END_TIME,
(CASE T.DESIRED_TIME WHEN ISNULL THEN MIN_TO_AMPM( TO_MINUTES(CONVERT (INT, AMPM2FULLHRS (T.EST_TIME)))) ELSE T.DESIRED_TIME END),
T.DISP, (CASE T.WHEEL_CHAIR WHEN 'Y' THEN 5 ELSE 2 END)
FROM DISPATCH_TRIPS T
WHERE T.R_NUM LIKE 'X%'

And the error msg that I am getting is this :

Server: Msg 195, Level 15, State 10, Procedure CREATE_TRIPS_SCHED, Line 25
'AMPM2FULLHRS' is not a recognized function name.
Server: Msg 156, Level 15, State 1, Procedure CREATE_TRIPS_SCHED, Line 56
Incorrect syntax near the keyword 'END'.
Server: Msg 156, Level 15, State 1, Procedure CREATE_TRIPS_SCHED, Line 68
Incorrect syntax near the keyword 'END'.

I have to do this conversion without introducing other fxns of mine. So, this is the actual problem. I hope I have been able to get across my problem!

Thx again! :)|||Pat,

At least you had the decency to be 'sorry' even if my turning 'rude' prompted you to do so.

Originally Posted by Pat Phelan
It does, but we still won't do your homework!
Having a few thousand posts does not give you the right to be judgemental. It also does not entitle you to speak on behalf of the whole forum.

P.S. Are u always like that?|||Perhaps adding the owner of the function when calling the function helps (db.owner.function)?|||It appears that my first impression may have been wrong. Based on your last post to blindman, you do seem to have a good grasp of SQL and a practical reason for making your original request.

I think you should have success if you use:SELECT @.Res = dbo.Fxn2 (CONVERT( INT, dbo.Fxn1() ))This ought to safely degenerate into:SELECT @.Res = dbo.Fxn2 (dbo.Fxn1() )In answer to your other question, yes, especially when this forum is barraged with kids looking for someone to do their homework for them, I tend to react with considerable skepticism to posts that are presented that way. My intent is to "red flag" those posts for other forum members, so that they will consider whether they want to spend their time working on a problem that is likely going to be turned in by a kid with no appreciation and no clue. The other forum members can use their own judgement, I'm just trying to make sure that they think before they "burn daylight" answering a post that I perceive as looking for an opportunity to plagerize.

I've repeatedly had my work turned in by students, whose teachers have recognized my style and contacted me. That probably makes me overly sensitive to the issue. I also think that there is a real ethical problem there too, and being in a position where ethical behavior is probably more important than technical expertise, that is very important to me.

Based on the begining of this thread, I had no reason to suspect that you were anything other than an apprentice juvenile delinquent, trolling to find someone that would do their assignment for them. Based on your post to Blindman, it appears that my first impression was wrong, so I responded differently in this post. As I've said before, I have no problem giving legitmate technical assistance, but I outright refuse to allow someone to take advantage of me (or anyone else without their approval).

-PatP|||Knighthassan,

Pat, myself, and others have given literally thousands of bits of advice and help to people who have posted on these boards, all of it free. If Pat made a mistake about your situation then so be it, but he had reasons to be suspicious. People asking for free advice should be less disrespectful, especially of people like Pat who is arguably the most knowledgable SQL Server expert on this forum. The fact that he took considerable time to review your complex code and propose a solution after last few posts you made shows how gracious he is. I hope his solution was helpful.|||blindman and Pat,

Thx to u both my problem is resolved. I also realize that my language has been a bit too harsh at times but something there got to me. Anyways, no hard feelings and thx again.|||blindman and Pat,

Thx to u both my problem is resolved. I also realize that my language has been a bit too harsh at times but something there got to me. Anyways, no hard feelings and thx again.No problem, and I'm glad that your problem has been fixed.

I rarely miss on issues like this, but of course when I do then it's a doozie. Out of several hundred "homework" calls that I've made, this is only the second one that I know where my initial call was wrong.

Right now I'm probably overly sensitive to it, since American schools are just coming back from the Thanksgiving holiday which meant that quite literally tens of thousands of assignments were due last week.

I noticed that you'd dived into some other message threads and contributed to them too. I hope that you decide to stick around and join in the fun! Lord knows that there is no shortage of questions, and it always helps to have a different perspective (although mine is usually quite "different" all by itself!).

-PatP|||I'm glad we (mosty Pat) were able to assist you, and yeah working with databases can be frustrating in itself sometimes, but we try to keep things light on this forum so no hard feelings and post again with both questions and advice.|||Pat, myself, and others have given literally thousands of bits of advice and help to people who have posted on these boards, all of it free.

Thank you

If Pat made a mistake about your situation then so be it, but he had reasons to be suspicious.

Umm, maybe not

People asking for free advice should be less disrespectful

Absolutely...why do people get so worked up? Who cares what soemone says? This always cracks me up.

especially of people like Pat who is arguably the most knowledgable SQL Server expert on this forum.

Well, could be, but I know Nigel posts here from time to time.

The fact that he took considerable time to review your complex code and propose a solution after last few posts you made shows how gracious he is. I hope his solution was helpful.

I think he'd still be having trouble. Post the actuall CREATE FUNCTION statements for both functions...personally they look more like a sproc to me...

And as for B. Lindman, you could probaly say "Yo, Blind dude!"

I always wondered where he got a braille keyboard..|||And as for B. Lindman, you could probaly say "Yo, Blind dude!"

I always wondered where he got a braille keyboard..Ah, Braille keyboards are no biggie to get. The difference is only in a few keys, but the tactile feedback for the indicators is the challenging part.

-PatP|||Wut's tuff iss finding a ceeing I dogg thata caan tipe woof a dam.

Bad dog! Bad!|||Originally Posted by Brett Kaiser
I always wondered where he got a braille keyboard..

A braille display and a braille mouse too! The world gets curiouser and unfathomable each passing day..

:D|||A braille display and a braille mouse too! The world gets curiouser and unfathomable each passing day..You say that as though it were a bad thing! ;)

As a side note, couldn't you get into some kind of trouble for feeling the bumps on a Braille mouse in the office ?

-PatP|||Depends on what you mean by mouse...