User Defined Function - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

User Defined Function

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2007-07-20 : 16:05:30

I am trying to create the following function can you please help.

*************************************************************
CREATE FUNCTION [dbo].[MyFunctionGETSeqNo](@orderID integer)
RETURNS VARCHAR(8000)
AS
BEGIN

SELECT ModuleRecordID AS COID from TableLinks
WHERE ModuleName = 'FO' AND LinkModuleRecordID = @orderID AND LinkModuleName = 'FN'

UNION ALL

SELECT LinkModuleRecordID AS COID from TableLinks
WHERE LinkModuleName = 'FO' AND ModuleRecordID = @orderID AND ModuleName = 'FN'

END

i will get one record or no record., but not multiple records (COID's)

i want to use that result COID, if i get any and use it in the following query to get the SeqNo

select SeqNo from tablefulfillment where COID= above resultCOID

and thats will be the result of this function.

Please help, that will be a great help..

Thank you very much.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-20 : 16:41:42

Your return type in the function is varchar, but your SELECT statement can return more than one record if there are matching records for both the SELECTs.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2007-07-20 : 17:00:01

Hello Dinaker,

The Union ALL select statement will only return one COID, definetely.

it is a number

quote:
Originally posted by dinakar

Your return type in the function is varchar, but your SELECT statement can return more than one record if there are matching records for both the SELECTs.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-20 : 17:07:14

What is the expected output? For a given ModuleRecordID = @orderID AND ModuleName = 'FN', will both columns ModuleRecordID and LinkModuleRecordID have values or only one of can have a value?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2007-07-22 : 08:10:21

Hello Dinaker,

Only one can have values. either or but we dont know until we execute which is holding the value,but defienetly 1 record will come.

quote:
Originally posted by dinakar

What is the expected output? For a given ModuleRecordID = @orderID AND ModuleName = 'FN', will both columns ModuleRecordID and LinkModuleRecordID have values or only one of can have a value?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-22 : 12:09:02

If the return values are numbers then why is the return type declared as varchar?


Declare @ID int
SELECT @Id= COALESCE(ModuleRecordID ,LinkModuleRecordID )
FROM TableLinks
WHERE LinkModuleName = 'FO' AND ModuleRecordID = @orderID AND ModuleName = 'FN'

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-07-23 : 01:15:37

I'm thinking that the function just doesn't work... there's no RETURN statement which is required in functions.

--Jeff Moden

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2007-07-23 : 11:46:21

Declare @ID int
SELECT @Id= COALESCE(ModuleRecordID ,LinkModuleRecordID )
FROM TableLinks
WHERE LinkModuleName = 'FO' AND ModuleRecordID = @orderID AND ModuleName = 'FN'

i think it won't work, because i tried to use union all.

and also in where condition first time is LinkModuleName = 'FO'

and second time

Where Modulename = 'FO'

only one condition i see in the above select.

And regarding the RETURNS VARCHAR(8000), that was a mistake, i should'nt have put varchar. over there.

************************************************

quote:
Originally posted by dinakar

If the return values are numbers then why is the return type declared as varchar?

Declare @ID int
SELECT @Id= COALESCE(ModuleRecordID ,LinkModuleRecordID )
FROM TableLinks
WHERE LinkModuleName = 'FO' AND ModuleRecordID = @orderID AND ModuleName = 'FN'

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-23 : 11:52:41

Post some sample code from the table and expected output.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2007-07-23 : 14:57:06

Hello Dinaker,

This is the query, i want to use, and the output result will be guaranteed 1 row.

SELECT ModuleRecordID AS COID from TableLinks
WHERE ModuleName = 'FO' AND LinkModuleRecordID = @orderID AND LinkModuleName = 'FN'

UNION ALL

SELECT LinkModuleRecordID AS COID from TableLinks
WHERE LinkModuleName = 'FO' AND ModuleRecordID = @orderID AND ModuleName = 'FN'

ALL i pass is parameter @orderID (i will pass 143) to execute the above selects.

Sample Data after executing the above two union select statements.

COID
--------
28 (which is an integer)
-------------------------------------------------------------------------------------------------------------------------------

And once i get the above COID, would like to execute the following select in the same userdefined function, if it is possible:

Select Orderid, Sequenceno, dateordered from TableOrders where COID= 28( the above reult id.)

here the result will be defienetly of 3 fields, int, real, date
------------------------------------------------------------------------------------------------------------------------------

Please help dinaker, if you can.

Thank you very much from the information.

quote:
Originally posted by dinakar

Post some sample code from the table and expected output.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-23 : 15:08:52

I am trying to, but you are not providing data. You are only saying what you want to do.
(1) You cannot return data from a function with a SELECT you have to get the data into a variable and return it via the variable.
(2) REPLACE the SELECT you have with what I provided (using the COALESCE) and get the value into the variable as declared above.
(3) put a RETURN @variable after the SELECT above.

Try the steps I mentioned. If you still cannot get it to work, post the modified code.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2007-07-24 : 11:27:39

Sorry Dinaker, i know i am also confusing you.

let me ask you this basic question: can i return multiple columns as output from one userdefined function.

all i am rtying to do is, first execute the select queries and get the ID, and use that ID to fire another select in the same userdefined function to get 5 columns data as output, meaning i can use the 5 columns data on the report.

Thank you very much.

quote:
Originally posted by dinakar

I am trying to, but you are not providing data. You are only saying what you want to do.
(1) You cannot return data from a function with a SELECT you have to get the data into a variable and return it via the variable.
(2) REPLACE the SELECT you have with what I provided (using the COALESCE) and get the value into the variable as declared above.
(3) put a RETURN @variable after the SELECT above.

Try the steps I mentioned. If you still cannot get it to work, post the modified code.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-24 : 11:53:22

quote:
Originally posted by cplusplus

Sorry Dinaker, i know i am also confusing you.

let me ask you this basic question: can i return multiple columns as output from one userdefined function.

NO. You can only return one value from a function. You can return a table too.

quote:
Originally posted by cplusplus

all i am rtying to do is, first execute the select queries and get the ID, and use that ID to fire another select in the same userdefined function to get 5 columns data as output, meaning i can use the 5 columns data on the report.

Thank you very much.

quote:
Originally posted by dinakar

I am trying to, but you are not providing data. You are only saying what you want to do.
(1) You cannot return data from a function with a SELECT you have to get the data into a variable and return it via the variable.
(2) REPLACE the SELECT you have with what I provided (using the COALESCE) and get the value into the variable as declared above.
(3) put a RETURN @variable after the SELECT above.

Try the steps I mentioned. If you still cannot get it to work, post the modified code.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Refer to my previous post. Follow the suggestions. Unless you change your script accordingly there's not much help I can provide, unfortunately.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources