Yagee


Is it possible to pass a table column value as a parameter for my User Defined function Coz mine doesnt work.

here's the function:

CREATE function [dbo].ufn_GetRefRange
(@RefType varchar(200)
, @RefValueFrom varchar(200)
, @RefValueTo varchar(200))
returns @tblRefRange table (RefValue varchar(200))
as
begin

Insert @tblRefRange(RefValue)
Select RefValue
from [dbo].tbl_Reference tr1
where rtrim(ltrim(RefType)) = rtrim(ltrim(@RefType))
and [Order] between
(select [order] from tbl_reference tr2
where rtrim(ltrim(tr1.RefType)) = rtrim(ltrim(tr2.RefType))
and RefValue = @RefValueFrom)
and
(select [order] from tbl_reference tr3
where rtrim(ltrim(tr1.RefType)) = rtrim(ltrim(tr3.RefType))
and RefValue = @RefValueTo)
order by [order]

return
end

here's how i call it. it returns a syntax error

Select top 500 * from [dbo].tbl_pds tpd

where (Select top 1 MktSeg from tbl_CDs where AcctNo = rtrim(ltrim('10200')))
-- in (select RefValue from ufn_GetRefRange(''MktgSeg'',tpd.MktgSegFrom,tpd.MktgSegTo))

but it works when i use this

-- in (select RefValue from ufn_GetRefRange(''MktgSeg'','A1','A3'))

Pls help thanks





Re: Inline table column pass as a paramter for User Defined Function?

Manivannan.D.Sekaran


It is not possible in SQL Server 2000, but if you use sql server 2005 then you can use CROSS APPLY.

See more about CROSS APPLY on the SQL Server Books Online.







Re: Inline table column pass as a paramter for User Defined Function?

Konstantin Kosinsky

In SQL Server 2005 you could use CROSS APPLY clause.

Change select RefValue from ufn_GetRefRange(''MktgSeg'',tpd.MktgSegFrom,tpd.MktgSegTo)

To:

Code Snippet

select RefValue from dbo.tbl_pds tpd

CROSS APPLY ufn_GetRefRange('MktgSeg',MktgSegFrom,MktgSegTo)






Re: Inline table column pass as a paramter for User Defined Function?

Yagee

sad to say our company uses only the 2000 version, but nway thanks for the help Smile at least i wont waste more time trying to use the function. =)




Re: Inline table column pass as a paramter for User Defined Function?

Yagee

thanks for the code snippet Smile i hope to use it someday when we finally decide to use 2005.




Re: Inline table column pass as a paramter for User Defined Function?

Manivannan.D.Sekaran

But, still there is a work around avilable for you. You have to give-up the Function & rewrite the query.

The following sample may help you to understand the work-around.

Code Snippet

Create Table [test$master] (

[ACNO] int ,

[ACNAME] Varchar(100)

);

Insert Into [test$master] Values('1','Mani');

Insert Into [test$master] Values('2','Yagee');

Insert Into [test$master] Values('3','Rajini');

Create Table [test$transaction] (

[TRNO] int ,

[ACNO] int ,

[DATE] datetime ,

[AMT] float

);

Insert Into [test$transaction] Values('1','1','1/8/2007','100');

Insert Into [test$transaction] Values('2','2','1/10/2007','200');

Insert Into [test$transaction] Values('3','1','1/11/2007','20');

Insert Into [test$transaction] Values('4','2','1/12/2007','30');

Insert Into [test$transaction] Values('5','3','1/13/2007','20');

Insert Into [test$transaction] Values('6','3','1/14/2007','100');

Insert Into [test$transaction] Values('7','3','1/15/2007','30');

Insert Into [test$transaction] Values('8','2','1/20/2007','40');

Insert Into [test$transaction] Values('9','1','1/30/2007','40');

Insert Into [test$transaction] Values('10','2','1/31/2007','200');

Go

Create Function [test$transactionlist] (@ACNO int)

Returns Table

as

Return(Select [ACNO], [TRNO], [DATE], [AMT] From [test$transaction] Where [ACNO]=@ACNO);

Code Snippet

--On SQL Server 2005

Select

[ACNAME]

,[DATE]

,[AMT]

From

[test$master] Master

CROSS APPLY [test$transactionlist](Master.ACNO) Trans

Code Snippet

/*

The following query will fail

Select

ACNAME

,[DATE]

,[AMT]

From

[test$master] Master

JOIN [test$transactionlist](Master.ACNO) Trans On Master.ACNO = Trans.ACNO

*/

Code Snippet

--The following query will work fine but not useful

Select

ACNAME

,[DATE]

,[AMT]

From

[test$master] Master

JOIN [test$transactionlist](1) Trans On Master.ACNO = Trans.ACNO

Code Snippet

--CROSS APPLY WORK-AROUND

--Rewriting the entire query as follow as

--Option 1 : Direct Query with base tables :: Recommended

Select

ACNAME

,[DATE]

,[AMT]

From

[test$master] Master

JOIN [test$transaction] Trans On Master.ACNO = Trans.ACNO

Code Snippet

--Option 2 : Using Cursor to fill the required data then re-use it

Create Table #Temp(

[ACNO] int ,

[TRNO] int ,

[DATE] datetime ,

[AMT] float

);

Declare @acno as Int;

Declare ACLIST Cursor

For Select Distinct ACNO from [test$master];

OPEN ACLIST

FETCH NEXT FROM ACLIST

INTO @acno

WHILE @@FETCH_STATUS = 0

BEGIN

Insert Into #Temp

Select * from [test$transactionlist](@acno);

FETCH NEXT FROM ACLIST

INTO @acno

END

CLOSE ACLIST

DEALLOCATE ACLIST

Select

ACNAME

,[DATE]

,[AMT]

From

[test$master] Master

JOIN #Temp Trans On Master.ACNO = Trans.ACNO

Drop table #Temp