Functions in Functions

Hi,

I have to calculate data in function with "EXEC". During runtime I get the Error:

"Only functions and extended stored procedures can be executed from within a function."

I would use a Stored Procedure, but the function is to be called from a view. I don't understand, why that should not be possible. Is there any way to shut that message down or to work around?

btw: Storing all the data in a table, would mean a lot of work, I rather not like to do. ;-)

Thx for any help

Blubb10

Wih in the function,

- You can't use dynamic SQL

- You can't call any stored proc

These are the limitation of the Function.

Post your soruce code.

|||Here is a thread describe the issue similar to yours.

F.Y.I.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=457236&SiteID=1

Thanks,

Zuomin
|||Without knowing OP's logic we can't simply declare it is by design.. Let him post the logic used inside the function.. We will wait.. |||

DECLARE @.decBOM_Count INT

DECLARE @.strBSC_Formula NVARCHAR(200)

-- For demo, is a parameter of the function

SELECT @.strBSC_Formula = 'BR-(MW-25)-8'

--the next steps are, replacing all the variables in the formula by actual number-values

.

.

.

-- Here @.strBSC_Formula contains something like '1000-(70-25)-8'

SELECT @.strBSC_Formula = 'SET @.decBOM_Count =' + @.strBSC_Formula

EXEC sp_executesql @.strBSC_Formula, N'@.decBOM_Count DECIMAL(10, 4) OUTPUT', @.decBOM_Count OUTPUT

-- In @.decBOM_Count I expect a number as result of the formula.

|||

Ok..

Here you can't achive it from the function directly.

If you use SQL Server 2000,

You have to use extended stored procedure

- a com program which will evaluate the given fromula and return back the value

- need to register that com dll on the db server

If you use SQL Server 2005,

You have to use the CLR function

- a simple C#/VB.NET code which will evulate the expression.

Let me know the version of SQL Server.. I will try to help you on this.

|||

It is SQL Server 2005 Standard and the program is written in Access 2003 / VBA.

functions in check constraint

Hi there,
Is it possible to modify a function used in a check constraint, without
having to drop the constraint first?
E.G.
Create function dbo.CheckSampleItemIssueStatus (@.sampleItemIssueId int,
@.StatusId int) Returns bit As
Begin
declare @.RetVal bit
if(@.StatusId = dbo.GetSampleItemIssueStatus(@.sampleItemIssueId))
Set @.RetVal = 1
else
Set @.RetVal = 0
Return @.RetVal
End
go
Alter table dbo.SampleItemIssue Add Constraint
CK_SampleItemIssue_StatusTypeId Check(
dbo.CheckSampleItemIssueStatus(SampleItemIssueId, StatusTypeId) = 1
)
go
Alter function dbo.CheckSampleItemIssueStatus(...
returns an error along the lines of cannot alter function because it is
referenced by constraint..
Thanks.
Fred.You have to drop the constraint first, before you can change the function.
What does the function GetSampleItemIssueStatus do? Because I think you can
solve this with foreign keys or otherwise without having to use functions.
--
Jacco Schalkwijk
SQL Server MVP
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:5EB25407-CCB1-4D31-A6A8-0AA62DB6D19D@.microsoft.com...
> Hi there,
> Is it possible to modify a function used in a check constraint, without
> having to drop the constraint first?
> E.G.
> Create function dbo.CheckSampleItemIssueStatus (@.sampleItemIssueId int,
> @.StatusId int) Returns bit As
> Begin
> declare @.RetVal bit
> if(@.StatusId = dbo.GetSampleItemIssueStatus(@.sampleItemIssueId))
> Set @.RetVal = 1
> else
> Set @.RetVal = 0
> Return @.RetVal
> End
> go
> Alter table dbo.SampleItemIssue Add Constraint
> CK_SampleItemIssue_StatusTypeId Check(
> dbo.CheckSampleItemIssueStatus(SampleItemIssueId, StatusTypeId) = 1
> )
> go
> Alter function dbo.CheckSampleItemIssueStatus(...
> returns an error along the lines of cannot alter function because it is
> referenced by constraint..
>
> Thanks.
> Fred.
>|||Thanks for the reply,
You confirmed my thoughts, I guess what I'm after is something like
Alter table disable/enable trigger, but for constraints.
That function is just an example and i can't do it via foreign keys,
because the rules governing the value of the statusId are based in part on
records from other tables.
In an other case I also need to check that a number matches the luhn
algorithm.
(http://www.brainyencyclopedia.com/encyclopedia/l/lu/luhn_algorithm.html)
Cheers.
"Jacco Schalkwijk" wrote:
> You have to drop the constraint first, before you can change the function.
> What does the function GetSampleItemIssueStatus do? Because I think you can
> solve this with foreign keys or otherwise without having to use functions.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Fred" <Fred@.discussions.microsoft.com> wrote in message
> news:5EB25407-CCB1-4D31-A6A8-0AA62DB6D19D@.microsoft.com...
> > Hi there,
> >
> > Is it possible to modify a function used in a check constraint, without
> > having to drop the constraint first?
> >
> > E.G.
> > Create function dbo.CheckSampleItemIssueStatus (@.sampleItemIssueId int,
> > @.StatusId int) Returns bit As
> > Begin
> > declare @.RetVal bit
> > if(@.StatusId = dbo.GetSampleItemIssueStatus(@.sampleItemIssueId))
> > Set @.RetVal = 1
> > else
> > Set @.RetVal = 0
> > Return @.RetVal
> > End
> > go
> >
> > Alter table dbo.SampleItemIssue Add Constraint
> > CK_SampleItemIssue_StatusTypeId Check(
> > dbo.CheckSampleItemIssueStatus(SampleItemIssueId, StatusTypeId) = 1
> > )
> > go
> >
> > Alter function dbo.CheckSampleItemIssueStatus(...
> >
> > returns an error along the lines of cannot alter function because it is
> > referenced by constraint..
> >
> >
> > Thanks.
> >
> > Fred.
> >
>
>

functions in check constraint

Hi there,
Is it possible to modify a function used in a check constraint, without
having to drop the constraint first?
E.G.
Create function dbo.CheckSampleItemIssueStatus (@.sampleItemIssueId int,
@.StatusId int) Returns bit As
Begin
declare @.RetVal bit
if(@.StatusId = dbo.GetSampleItemIssueStatus(@.sampleItemIssueId))
Set @.RetVal = 1
else
Set @.RetVal = 0
Return @.RetVal
End
go
Alter table dbo.SampleItemIssue Add Constraint
CK_SampleItemIssue_StatusTypeId Check(
dbo.CheckSampleItemIssueStatus(SampleItemIssueId, StatusTypeId) = 1
)
go
Alter function dbo.CheckSampleItemIssueStatus(...
returns an error along the lines of cannot alter function because it is
referenced by constraint..
Thanks.
Fred.
You have to drop the constraint first, before you can change the function.
What does the function GetSampleItemIssueStatus do? Because I think you can
solve this with foreign keys or otherwise without having to use functions.
Jacco Schalkwijk
SQL Server MVP
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:5EB25407-CCB1-4D31-A6A8-0AA62DB6D19D@.microsoft.com...
> Hi there,
> Is it possible to modify a function used in a check constraint, without
> having to drop the constraint first?
> E.G.
> Create function dbo.CheckSampleItemIssueStatus (@.sampleItemIssueId int,
> @.StatusId int) Returns bit As
> Begin
> declare @.RetVal bit
> if(@.StatusId = dbo.GetSampleItemIssueStatus(@.sampleItemIssueId))
> Set @.RetVal = 1
> else
> Set @.RetVal = 0
> Return @.RetVal
> End
> go
> Alter table dbo.SampleItemIssue Add Constraint
> CK_SampleItemIssue_StatusTypeId Check(
> dbo.CheckSampleItemIssueStatus(SampleItemIssueId, StatusTypeId) = 1
> )
> go
> Alter function dbo.CheckSampleItemIssueStatus(...
> returns an error along the lines of cannot alter function because it is
> referenced by constraint..
>
> Thanks.
> Fred.
>
|||Thanks for the reply,
You confirmed my thoughts, I guess what I'm after is something like
Alter table disable/enable trigger, but for constraints.
That function is just an example and i can't do it via foreign keys,
because the rules governing the value of the statusId are based in part on
records from other tables.
In an other case I also need to check that a number matches the luhn
algorithm.
(http://www.brainyencyclopedia.com/en...algorithm.html)
Cheers.
"Jacco Schalkwijk" wrote:

> You have to drop the constraint first, before you can change the function.
> What does the function GetSampleItemIssueStatus do? Because I think you can
> solve this with foreign keys or otherwise without having to use functions.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Fred" <Fred@.discussions.microsoft.com> wrote in message
> news:5EB25407-CCB1-4D31-A6A8-0AA62DB6D19D@.microsoft.com...
>
>

Functions don't show dependencies?

I use a user defined function in several stored procedures. However, only
tables show up when I look at the function's dependencies. How do I also
have the stored procedures that are using this function show up as
dependencies?
If that cannot be done, how else do I keep track of the stored procedures
which are using a certain function?
Thanks,
BrettThis works for me. Try recreating the sps.
use northwind
go
create function dbo.ufn_f1 ()
returns int
as
begin
return (1)
end
go
create procedure dbo.usp_p1
as
select dbo.ufn_f1()
go
exec sp_depends ufn_f1
go
drop procedure dbo.usp_p1
go
drop function dbo.ufn_f1
go
AMB
"Brett" wrote:

> I use a user defined function in several stored procedures. However, only
> tables show up when I look at the function's dependencies. How do I also
> have the stored procedures that are using this function show up as
> dependencies?
> If that cannot be done, how else do I keep track of the stored procedures
> which are using a certain function?
> Thanks,
> Brett
>
>|||Also,
How do I find a stored procedure containing <text>?
http://www.aspfaq.com/show.asp?id=2037
AMB
"Alejandro Mesa" wrote:
> This works for me. Try recreating the sps.
> use northwind
> go
> create function dbo.ufn_f1 ()
> returns int
> as
> begin
> return (1)
> end
> go
> create procedure dbo.usp_p1
> as
> select dbo.ufn_f1()
> go
> exec sp_depends ufn_f1
> go
> drop procedure dbo.usp_p1
> go
> drop function dbo.ufn_f1
> go
>
> AMB
>
> "Brett" wrote:
>

functions DIFFERENCE() and SOUNDEX()

Hi!
Is there any other function that can compares two strings ? I'm using the
functions DIFFERENCE() and SOUNDEX(), but they don't consider vowels, "y" and
"h", and I need something that compares everything!
thanks
--
Message posted via http://www.sqlmonster.comOn Fri, 09 Sep 2005 15:37:43 GMT, Amaury Coria via SQLMonster.com wrote:
>Hi!
>Is there any other function that can compares two strings ? I'm using the
>functions DIFFERENCE() and SOUNDEX(), but they don't consider vowels, "y" and
>"h", and I need something that compares everything!
>thanks
Hi Amaury,
What exactly do you mean with "compares everything"? If you are looking
for completely equal strings, just use the '=' operator. The SOUNDEX and
DIFFERENCE functions are deliberately leaving out certain parts of the
string, since they are intended to find common misspelling of words or
names. And in case you and/or your users are not English, beware that
they are designed for English.
If you want to find ""almost equal" strings but are not satisfied with
the algorithm used in SOUNDEX and DIFFERENCE, you'll have to create your
own functions for it. I have no experience with this kind of string
handling, but I believe that several algorithms for this kind of task
are out there on the internet. Google is your friend!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||There's an interesting article about alternative (better) soundex-like
schemes at http://www.avotaynu.com/soundex.html.
Paul Shapiro
"Amaury Coria via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:541CE9D86DFA7@.SQLMonster.com...
> Is there any other function that can compares two strings ? I'm using the
> functions DIFFERENCE() and SOUNDEX(), but they don't consider vowels, "y"
> and
> "h", and I need something that compares everything!

functions DIFFERENCE() and SOUNDEX()

Hi!
Is there any other function that can compares two strings ? I'm using the
functions DIFFERENCE() and SOUNDEX(), but they don't consider vowels, "y" an
d
"h", and I need something that compares everything!
thanks
Message posted via http://www.droptable.comOn Fri, 09 Sep 2005 15:37:43 GMT, Amaury Coria via droptable.com wrote:

>Hi!
>Is there any other function that can compares two strings ? I'm using the
>functions DIFFERENCE() and SOUNDEX(), but they don't consider vowels, "y" a
nd
>"h", and I need something that compares everything!
>thanks
Hi Amaury,
What exactly do you mean with "compares everything"? If you are looking
for completely equal strings, just use the '=' operator. The SOUNDEX and
DIFFERENCE functions are deliberately leaving out certain parts of the
string, since they are intended to find common misspelling of words or
names. And in case you and/or your users are not English, beware that
they are designed for English.
If you want to find ""almost equal" strings but are not satisfied with
the algorithm used in SOUNDEX and DIFFERENCE, you'll have to create your
own functions for it. I have no experience with this kind of string
handling, but I believe that several algorithms for this kind of task
are out there on the internet. Google is your friend!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||There's an interesting article about alternative (better) soundex-like
schemes at http://www.avotaynu.com/soundex.html.
Paul Shapiro
"Amaury Coria via droptable.com" <forum@.droptable.com> wrote in message
news:541CE9D86DFA7@.droptable.com...
> Is there any other function that can compares two strings ? I'm using the
> functions DIFFERENCE() and SOUNDEX(), but they don't consider vowels, "y"
> and
> "h", and I need something that compares everything!

functions DIFFERENCE() and SOUNDEX()

Hi!
Is there any other function that can compares two strings ? I'm using the
functions DIFFERENCE() and SOUNDEX(), but they don't consider vowels, "y" and
"h", and I need something that compares everything!
thanks
Message posted via http://www.droptable.com
On Fri, 09 Sep 2005 15:37:43 GMT, Amaury Coria via droptable.com wrote:

>Hi!
>Is there any other function that can compares two strings ? I'm using the
>functions DIFFERENCE() and SOUNDEX(), but they don't consider vowels, "y" and
>"h", and I need something that compares everything!
>thanks
Hi Amaury,
What exactly do you mean with "compares everything"? If you are looking
for completely equal strings, just use the '=' operator. The SOUNDEX and
DIFFERENCE functions are deliberately leaving out certain parts of the
string, since they are intended to find common misspelling of words or
names. And in case you and/or your users are not English, beware that
they are designed for English.
If you want to find ""almost equal" strings but are not satisfied with
the algorithm used in SOUNDEX and DIFFERENCE, you'll have to create your
own functions for it. I have no experience with this kind of string
handling, but I believe that several algorithms for this kind of task
are out there on the internet. Google is your friend!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||There's an interesting article about alternative (better) soundex-like
schemes at http://www.avotaynu.com/soundex.html.
Paul Shapiro
"Amaury Coria via droptable.com" <forum@.droptable.com> wrote in message
news:541CE9D86DFA7@.droptable.com...
> Is there any other function that can compares two strings ? I'm using the
> functions DIFFERENCE() and SOUNDEX(), but they don't consider vowels, "y"
> and
> "h", and I need something that compares everything!

Functions

I have a subquery that is running really slow when i use a date from the parent query

* the function udfMinContact returns a table of the earliest contact for every child that occured after the date passed to the function

* the function udfMaxReferral returns a table of the latest Referral for every child that occured before the date passed to the function

* the referral happens first, then the child is contacted. i'm looking for contacts that happed over 45 days after the referral
*************************************************************************************
DECLARE
@.EndDate DateTime,
@.StartDate DateTime

SET @.StartDate = '4/1/2007'
SET @.EndDate = '6/30/2007'

SELECT c.ChildId, c.FN, c.LN, c.DOB

FROM Child c INNER JOIN udfMinContact(@.StartDate) ct ON c.ChildID = ct.ChildId

WHERE ct.ContactDate BETWEEN @.StartDate AND @.EndDate

AND EXISTS
(
SELECT ChildId
FROM udfMaxReferral(ct.ContactDate) r
WHERE r.ChildId = c.ChildId
AND r.ReferralDate < DATEADD(dd, -45, ct.ContactDate)

)
*********************************************************************************************

If i run as is, it takes over 40 min. If i replace 'ct.ContactDate' which i highlighted with a static date like '1/1/2007' it runs in just a few seconds.

any idea why the drastic time difference and any suggestions on how to speed it up?

thanks

Check the Execution Plan.

Your FUNCTION has to fully execute for each and every row in the table, perhaps two times per row if it needs to re-calculate for the sub-query..

You may be able to substanially improve execution speed if you JOIN with the data that has the earliest contact INSTEAD of using the function. (I'm assuming that the function is a query.)

Please post the entire FUNCTION code and we can better determine the optimal way to deal with your issue.

|||

Is udfMaxReferral a multi-statement or inline TVF? Look at the query plan to see how the join is being done. If it is a nested loop join then it is possible that the TVF is invoked for every row that is being joined in the outer SELECT statement. Also, if the TVF is multi-statement then there are no statistics on the rows being returned so the plan will be sub-optimal. You should consider using inline TVF so that the query can be optimized as a whole.

Now, as for the question why if you use a variable or column in the TVF it is slower than a value or constant is due to plan caching and query optimization. When you specify a constant in a predicate or parameter to SP or function etc then the query optimizer can use that value and determine the best plan based on the available statistics. On the other hand, if you specify a variable or column then the value is not known and it can be any value within the domain of a data type so the query optimizer will pick a plan that works optimally for any search value. See the white paper on compilation, recompilation for more details on how this works.

|||Function: udfMinContact
Description: Returns info on the earliest 'IF' Contact (that occured on or after the StartDate) for every child

ALTER FUNCTION [dbo].[udfMinContact]
(
@.StartDate datetime
)
RETURNS @.retChildList TABLE
(
ChildId uniqueidentifier,
ContactId uniqueidentifier,
ContactDate datetime
)

AS
BEGIN

INSERT @.retChildList
SELECT ChildId, ContactId, ContactDate
FROM Contact ct
WHERE ContactId =
(
SELECT TOP(1) ContactId
FROM Contact
WHERE ChildId = ct.ChildId
AND ContactDate >= @.StartDate
ORDER BY ContactDate ASC, CREATE_TIME ASC
)

RETURN
END;

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

Function: udfMaxReferral
Description: Returns info on the most current Referral (that occured on or before the EndDate) for every child

ALTER FUNCTION [dbo].[udfMaxReferral]
(
@.EndDate datetime
)
RETURNS @.retChildList TABLE
(
ChildId uniqueidentifier,
ReferralId uniqueidentifier,
ReferralDate datetime
)

AS
BEGIN

INSERT @.retChildList
SELECT ChildId, ReferralId, ReferralDate
FROM Referral r
WHERE ReferralId =
(
SELECT TOP(1) ReferralId
FROM Referral
WHERE ChildId = r.ChildId
AND ReferralDate <= @.EndDate
ORDER BY ReferralDate DESC, CREATE_TIME DESC
)

RETURN
END;

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

I know the functions are not ideal, but the way the database is set up, it's the best i could do. there are many cases where a child will have several contacts or referrals on the same day so this is how i forced only 1 to be returned
|||

you can try this, and check if it can change your query speed,

notice, the get_datetime is a function needed you defined it.

i think ORDER BY clause always waste resource!!!

ALTER FUNCTION [dbo].[udfMaxReferral]
(
@.EndDate datetime
)
RETURNS @.retChildList TABLE
(
ChildId uniqueidentifier,
ReferralId uniqueidentifier,
ReferralDate datetime
)

AS
BEGIN

INSERT @.retChildList
SELECT ChildId, ReferralId, ReferralDate
FROM Referral a inner join

(

SELECT ChildId,ReferralId,MAX(get_datetime(ReferralDate,ReferralTime) datetime
FROM Referral

WHERE ReferralDate <= @.EndDate

GROUP BY ChildId,ReferralId

) b on a.ChildId=b.ChildId and a.ReferralId=ReferralId.ReferralId and get_datetime(a.ReferralDate,a.ReferralTime)=b.datetime

WHERE ReferralDate <= @.EndDate

RETURN
END;

|||

As per Uma's suggestion you have to convert your Multilined TVF to Inline TVF,

Use the following functions,

Code Snippet

CREATE FUNCTION [dbo].[udfMinContact]

(

@.StartDate datetime

)

RETURNS TABLE

AS

RETURN (

SELECT ChildId, ContactId, ContactDate

FROM Contact ct

WHERE ContactId =

(

SELECT TOP(1) ContactId

FROM Contact

WHERE ChildId = ct.ChildId

AND ContactDate >= @.StartDate

ORDER BY ContactDate ASC, CREATE_TIME ASC

)

)

GO

CREATE FUNCTION [dbo].[udfMaxReferral]

(

@.EndDate datetime

)

RETURNS TABLE

AS

RETURN

(

SELECT ChildId, ReferralId, ReferralDate

FROM Referral r

WHERE ReferralId =

(

SELECT TOP(1) ReferralId

FROM Referral

WHERE ChildId = r.ChildId

AND ReferralDate <= @.EndDate

ORDER BY ReferralDate DESC, CREATE_TIME DESC

)

)

|||

Manivannan.D.Sekaran wrote:

As per Uma's suggestion you have to convert your Multilined TVF to Inline TVF,

What is the difference? Sorry I'm still pretty new to anything more the simple SQL

functions

I need to do a LEAST() and Greatest() function in SQL query.

Is this possible in anyway shape or form?

Here is an example...

tblTable.oneID = 4
tblTable.twoID = 5

SELECT ... LEAST(tblTable.oneID,tblTable.twoID) FROM tblTable

and it returns 4. Or in the GREATEST function, it would return 5.

Please help.

Thanks in advanceI think i figured it out...

LEAST function = SELECT (CASE WHEN oneID < twoID THEN oneID ELSE twoID) as LeastOFtheTWO FROM ...

GREATEST function = SELECT (CASE WHEN oneID > twoID THEN oneID ELSE twoID) as LeastOFtheTWO FROM ...|||You are on the right track. Try the following for the least case:

select case when (a < b) then a else b end
from (select min(field1) as a, min(field2) as b from table) as abc

Functions

I have a string argument in a stored procedure that returns a string
value. I would like to replace that string argument with a function.
Does anyone know what the syntax would be?
Here is an example:
exec sp_send_cdosysmail
'sqladmin@.mycompany.com','DBAeMailAddress@.mycompany.com','Subject Of
e-mail','Body of e-mail message'
I would like to replace DBAeMailAddress@.mycompany.com with a function.
I already have the function written and it does work successfully but
not with the function call from within the arguments for the stored
procedure.
If I can find a way to successfully implement this, I can change the
on-call DBA's name in the function instead of within every single
scheduled job.
Toni
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You cannot call a function inside a stored procedure call. But why not
declare a local variable assign a value to the variable (using the function
call) and then use that local variable in the parameter list?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Toni" <teibner@.allina.com> wrote in message
news:e2OjRW7pDHA.2012@.TK2MSFTNGP12.phx.gbl...
> I have a string argument in a stored procedure that returns a string
> value. I would like to replace that string argument with a function.
> Does anyone know what the syntax would be?
> Here is an example:
> exec sp_send_cdosysmail
> 'sqladmin@.mycompany.com','DBAeMailAddress@.mycompany.com','Subject Of
> e-mail','Body of e-mail message'
> I would like to replace DBAeMailAddress@.mycompany.com with a function.
> I already have the function written and it does work successfully but
> not with the function call from within the arguments for the stored
> procedure.
> If I can find a way to successfully implement this, I can change the
> on-call DBA's name in the function instead of within every single
> scheduled job.
>
> Toni
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

FUNCTIONS

I just installed SSRS 2005 and I have experience with SQL.

How come this function does not work?

SELECT SUBSTRING(YEAR_MONTH, 1, 2) AS Expr1
FROM table1

I get a message which states that this command is not supported by the provider?

It works fine with other SQL tools like winsql?

thanks

Refer to the responses to your identical post in the Transact_SQL forum.

Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).

Functions

Hi,,

I'm having a problem with calling a function from an activex script
within a data transformation. the function takes 6 inputs and returns
a single output. My problem is that after trying all of the stuff on
BOL I still can't get it to work. It's on the same database and I'm
running sql 2000.

when I try to call it I get an error message saying "object required
functionname" If I put dbo in front of it I get "object required dbo".

Can anyone shed any light on how i call this function and assign the
output value returned to a variable name.

thanks.Mirth1314 (not@.ahope.net) writes:
> I'm having a problem with calling a function from an activex script
> within a data transformation. the function takes 6 inputs and returns
> a single output. My problem is that after trying all of the stuff on
> BOL I still can't get it to work. It's on the same database and I'm
> running sql 2000.
> when I try to call it I get an error message saying "object required
> functionname" If I put dbo in front of it I get "object required dbo".
> Can anyone shed any light on how i call this function and assign the
> output value returned to a variable name.

Please post the code you are using. Both the code for the UDF and
the Active-X code you use to call it.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland,

To be honest I can't really do that but as i say I was on books online
and I assumed I could just declare a variable and assign it to the
value returned from the function.

So i have been trying...

variable = functionName(val1, val2, val3, val4, val5, val6)

The function is declared as...

create function owner.functionName(@.val1 datatype, @.val 2datetype ...
@.val6 datatype) returns datatype
begin
.....
return @.returnValName
end

I hope this helps as I can't really expand any further. My help was
more of a guideline or an example of how to do this as opposed to
specific help.

Thanks.

On Sun, 14 Sep 2003 18:13:34 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:

>Mirth1314 (not@.ahope.net) writes:
>> I'm having a problem with calling a function from an activex script
>> within a data transformation. the function takes 6 inputs and returns
>> a single output. My problem is that after trying all of the stuff on
>> BOL I still can't get it to work. It's on the same database and I'm
>> running sql 2000.
>>
>> when I try to call it I get an error message saying "object required
>> functionname" If I put dbo in front of it I get "object required dbo".
>>
>> Can anyone shed any light on how i call this function and assign the
>> output value returned to a variable name.
>Please post the code you are using. Both the code for the UDF and
>the Active-X code you use to call it.|||Mirth1314 (not@.ahope.net) writes:
> To be honest I can't really do that but as i say I was on books online
> and I assumed I could just declare a variable and assign it to the
> value returned from the function.

If you don't post your code, your chances to get help are reduced.
You will have to excuse, but guessing you might be doing wrong is
not that thrilling.

You don't have to post your actual code, but some sample, and which
demonstrates the same problem as your original code.

> So i have been trying...
> variable = functionName(val1, val2, val3, val4, val5, val6)

Don't know if this is supposed to be Active-X or T-SQL. In T-SQL
the syntax is

EXEC @.variable = dbo.fun(@.par1, @.par2, ...)

In Active-X I don't know, as I don't really know what Active-X is. (See
know why I need a real code sample?) I suppose it involves ADO (after
Active-X is what the A stands for), and honestly I don't know if you can
call UDFs directly from ADO. Again, that's why I want a sample to work
from.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||CREATE FUNCTION fnTEST
(@.param1 int, @.param2 int)
RETURNS int
AS
BEGIN
DECLARE @.sum AS int
SELECT @.sum = @.param1 + @.param2
RETURN @.sum
END

go

declare @.ReturnVariable int
select @.ReturnVariable=dbo.fntest(1,1)
select @.ReturnVariable

Not surprisingly this will disply 2 in query analyser, but it does
serve the point of displaying how to assign a functions return value
to a variable.|||Ok thanks guys. I appreciate how difficult this is for you without
having the code but you are helping.

I can now get the code to work in query analyser using DMAC's stuff
below.

However i need to call this function as part of a DTS package inside a
Transform Data Task, that's where the activex package comes in Erland.
Written in VBScript.

When I try to run/execute/call it I get error code:0; vbscript runtime
error; Type Mismatch: functionanme.

Now my impression was that it has something to do with datatypes. So I
explicitly set the date fields using cdate and the varchar fields
using cstr but I still get the error.

I'm still trying to call it by using...

variable name = functionname(var1, var2 ...var6)

Am I missing the boat here, can it be done and if not can someone shed
some light on the best way to use a UDF like this inside a data
transformation task.

Thanks again guys.

On 15 Sep 2003 16:03:47 -0700, drmcl@.drmcl.free-online.co.uk (DMAC)
wrote:

>CREATE FUNCTION fnTEST
>(@.param1 int, @.param2 int)
>RETURNS int
>AS
>BEGIN
> DECLARE @.sum AS int
> SELECT @.sum = @.param1 + @.param2
> RETURN @.sum
>END
>go
>declare @.ReturnVariable int
>select @.ReturnVariable=dbo.fntest(1,1)
>select @.ReturnVariable
>Not surprisingly this will disply 2 in query analyser, but it does
>serve the point of displaying how to assign a functions return value
>to a variable.|||Mirth1314 (not@.ahope.net) writes:
> However i need to call this function as part of a DTS package inside a
> Transform Data Task, that's where the activex package comes in Erland.
> Written in VBScript.

I have no experience of VBscript, and I don't use DTS. I think that
maybe you should jog over to microsoft.public.sqlserver.dts. The
people over there, might understand better what you are doing.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland, I'll have a look over there. Although I did think that
what I was doing here was fairly straight forward in sqlserver world.
I can't possibly imagine I'm the only person who has ever tried to
call or use a UDF from a data transformation task.

On Tue, 16 Sep 2003 20:52:31 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:

>Mirth1314 (not@.ahope.net) writes:
>> However i need to call this function as part of a DTS package inside a
>> Transform Data Task, that's where the activex package comes in Erland.
>> Written in VBScript.
>I have no experience of VBscript, and I don't use DTS. I think that
>maybe you should jog over to microsoft.public.sqlserver.dts. The
>people over there, might understand better what you are doing.

Function works on SQLServer but not on MSDE

I have spent considerable time trying to debug this one without
success.
I have a database which runs on my client's SQLServer 2000. It
contains a scalar-valued text function which works fine.
Using the same function definition in the same way on MSDE it returns
the wrong result. I am baffled. Is there some incompatibility I
should know about?
The function (simplified) goes like this
ALTER FUNCTION dbo.fnDoseRate
(
@.ID Int,
@.WhichDoseRate Int
)
RETURNS Float
AS
BEGIN
DECLARE @.Dose Float
IF @.WhichDoseRate=1
SELECT @.Dose = 1.2
IF @.WhichDoseRate=2
SELECT @.Dose = 2.3
IF @.WhichDoseRate=3
SELECT @.Dose = 3.4
RETURN @.Dose
END
I call it from a query
SELECT X, Y, dbo.fnDoseRate(1,1), dbo.fnDoseRate(1,2),
dbo.fnDoseRate(2,3) FROM MyTable
and the query delivers the expected results
If I change the query to
SELECT X, Y, SUM(dbo.fnDoseRate(1,1)), SUM(dbo.fnDoseRate(1,2)),
SUM(dbo.fnDoseRate(2,3)) FROM MyTable GROUP BY X, Y
and the first 2 of the SUM fields return the same value (appropriate
only to 1,1). If I change the last call to 1,3 it gives the same wrong
value.
Any ideas?
Bill Manville
MVP - Microsoft Excel, Oxford, England
FWIW I should add that I created the function definition and the view
using an Access2002 ADP project.
Bill Manville
MVP - Microsoft Excel, Oxford, England
|||Are both engines at the same service pack level?
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Bill Manville" <Bill-Manville@.msn.com> wrote in message
news:VA.000013fd.3da5fb80@.msn.com...
> FWIW I should add that I created the function definition and the view
> using an Access2002 ADP project.
> Bill Manville
> MVP - Microsoft Excel, Oxford, England
>
|||>Are both engines at the same service pack level?
Good question.
The client controls the remote server so I don't readily know about
that. Is there a way I could interrogate it to find out?
Nor am I sure how to find out the service pack level of my MSDE; I'm a
bit of an amateur in this area! Can you point me in the right
direction?
Looking at SysInfo > Loaded Modules, I see
sqlserver 2000.080.0194.00
Is that the correct place to look? And is that the most recent
version? If not, where should I go to update it?
(I run Microsoft Update regularly but I guess it might not reach MSDE)
Bill Manville
MVP - Microsoft Excel, Oxford, England
|||SELECT @.@.Version
returns:
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52
Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows
NT 5.1 (Build 2600: Service Pack 2)
Ah, I'm running on XP which is reported (in this case) as NT 5.1.
It seems to me there is a Microsoft site that lists the versions and the
service packs etc. associated with each. I'm pretty sure I have SS 2005 SP2
installed here.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Bill Manville" <Bill-Manville@.msn.com> wrote in message
news:VA.000013fe.3fc7c2fb@.msn.com...
> Good question.
> The client controls the remote server so I don't readily know about
> that. Is there a way I could interrogate it to find out?
> Nor am I sure how to find out the service pack level of my MSDE; I'm a
> bit of an amateur in this area! Can you point me in the right
> direction?
> Looking at SysInfo > Loaded Modules, I see
> sqlserver 2000.080.0194.00
> Is that the correct place to look? And is that the most recent
> version? If not, where should I go to update it?
> (I run Microsoft Update regularly but I guess it might not reach MSDE)
>
> Bill Manville
> MVP - Microsoft Excel, Oxford, England
>
|||OK, so my MSDE is
Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000
00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Personal
Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
and my client's server is
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005
23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard
Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
I guess that indicates my MSDE is a bit out of date.
Now to find out how to get it updated...
Bill Manville
MVP - Microsoft Excel, Oxford,
|||Ah, it's looks like it--perhaps dangerously so. It might still be prone to
the network attacks the pre SP2 versions faced years ago.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Bill Manville" <Bill-Manville@.msn.com> wrote in message
news:VA.000013ff.412751a3@.msn.com...
> OK, so my MSDE is
> Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
> Aug 6 2000
> 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation
> Personal
> Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
>
> and my client's server is
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
> May 3 2005
> 23:18:38
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard
> Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
>
> I guess that indicates my MSDE is a bit out of date.
> Now to find out how to get it updated...
> Bill Manville
> MVP - Microsoft Excel, Oxford,
>

function within view

I have a view built like this
CREATE VIEW XXX
AS
select * from XXX_Calculate()
the XXX_calculate() function is built like this
CREATE FUNCTION XXX_Calculate ()
RETURNS @.Result TABLE (XXXID bigint NOT NULL, XXX2ID bigint NOT NULL)
....
it contains cursors which insert values to the @.Result table.
The problem is whenever a user calls this view the function is beiing
executed again so the retrieval is slow...
Is there a hint to have it behave like normal view?
Thanx in advance.
Sorry about my poor English...P Platan:
At first i'm trying to to use cursors at all.
Because as you see it works very slow, i would offer you to think how to be
avoid usin cursor.
If your example is as well as your view', I dont see whay do you need view
at all. On many programs that use sql you can use SELECT * fron function().
I would offer you to use store procedure instead of view. because on store
procedure you can set the function result on one temporary table and use it
as you can in the store procedure. Also all other software who work with sql
server can use store procedure as well as view.
"P Platan" <pplat@.exnds.com> wrote in message
news:ulItixdQGHA.4536@.TK2MSFTNGP10.phx.gbl...
>I have a view built like this
> CREATE VIEW XXX
> AS
> select * from XXX_Calculate()
> the XXX_calculate() function is built like this
> CREATE FUNCTION XXX_Calculate ()
> RETURNS @.Result TABLE (XXXID bigint NOT NULL, XXX2ID bigint NOT NULL)
> ....
> it contains cursors which insert values to the @.Result table.
> The problem is whenever a user calls this view the function is beiing
> executed again so the retrieval is slow...
> Is there a hint to have it behave like normal view?
> Thanx in advance.
> Sorry about my poor English...
>|||I use view because it resides in another database from the one that the
function calls.
To be more specific
In the old datbase schema we had a basic table with 150 categories as fields
I the new implementation we want to normalize it and have them 'vertical'.
In order not to transfer lots of data to the other db and not load triggers
in the basic table which is accessed very heavily we created the view to the
new db which 'verticals' the categories-fields of the basic table.
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:uNX6wjeQGHA.5248@.TK2MSFTNGP09.phx.gbl...
>P Platan:
> At first i'm trying to to use cursors at all.
> Because as you see it works very slow, i would offer you to think how to
> be avoid usin cursor.
> If your example is as well as your view', I dont see whay do you need view
> at all. On many programs that use sql you can use SELECT * fron
> function().
> I would offer you to use store procedure instead of view. because on store
> procedure you can set the function result on one temporary table and use
> it as you can in the store procedure. Also all other software who work
> with sql server can use store procedure as well as view.
> "P Platan" <pplat@.exnds.com> wrote in message
> news:ulItixdQGHA.4536@.TK2MSFTNGP10.phx.gbl...
>

Function with unlimited parameters

Is there any way to write procedure with ulimited number of parameters?

Like in COALESCE function. You can pass one or more parameters.

The short answer would be no, it's not.
(there is a hard limit on # parameters, but if you ever get there, you're in deep trouble most likely)

Why would you need it? Don't you know beforehand what the procedure will do?
There is often a higher cost in reaching for the ultimate in generic, instead of specializing, which will do 'less' but with lower overhead and less test/development/maintenance time.

Keep it simple, and it will work forever =:o)

/Kenneth

|||

Nope, you can't even write a function like that where you can skip parameters. I have a date function that I need to be able to pass "unlimited" values to but I have only 16 works right now. Even worse you would have to default every parameter too.

dbo.function ('value1','value2',null,null,null,null,null,null,null,null,null,null,null,null,null,...)

As an alternative (yet more costly method) you could pass a comma delimited list as a string parameter and split it up into your N values. If that is a reasonable possibility, then you can look here for how to do this: http://www.sommarskog.se/arrays-in-sql.html. XML is another possibility. Both of these require work on your end to compile the string of course, so that might not be a great way to go either.

If that doesn't make sense, someone here can write you a query based on the data you have.

|||I believe you can create an extended stored procedure that can take an unlimited number of parameters.|||Maybe You know how?

Some example?|||

Extended stored procedures are marked for deprecation so don't plan on writing new code since you will have to convert again in couple of releases or so. What is the problem you are trying to solve? Why do you need to pass optional parameters? You could also use a temporary table for example to pass the parameter values as rows. So it depends on what functionality you want and why.

|||My question is purely teoretical. There is no some kind of problem I'am trying to solve with this solution.

Function with local parameters

I am trying to write a function that returns different values regdaring to a
input value.
Why can't I write like this. What shoudl I do instead.
I want to be able to write a select Item from GetPermission ('user', 're')
Regards
/Magnus
alter function [dbo].[GetPermissions3]
(
@.UserName varchar(50),
@.ItemName varchar(50),
)
RETURNS TABLE
AS
declare @.objecttype int
if @.ItemType='RE' set @.objecttype=1
RETURN
(
If
select i.Item, o.[name] from ItemGroups i
full outer join Groups g
on g.ItemGroup = i.ItemGroup
full outer join Users u
on u.GroupName = g.GroupName
join Control.dbo.Object o on i.item = o.object and o.no=@.objecttype
where (UserName = @.UserName or i.ItemGroup = @.UserName) and ItemName =
@.ItemName
)Please have a look at the syntax used to create functions. You are
attempting to create an inline function which must not have a function body
and which must contain a single select query within the returns clause. If
you want to include logic that cannot be handled within a single select
statement, then you will need to use the more complex table-valued function
syntax.|||Thanks!
After some searching for complex table-valued function I found the syntax
necessary!
My simple test (if someone wants to know) became:
alter FUNCTION MBTest
( @.FirstColNumber int )
RETURNS @.MyTable TABLE
( FirstCol varchar(50),
SecondCol varchar(50) )
AS BEGIN
declare @.mynum varchar(50)
select @.mynum = case @.FirstColNumber
when 0 then 'zero'
when 1 then 'one' end
insert @.MyTable (FirstCol,SecondCol) select @.mynum,FirstName from
BookingsApril2006
RETURN END
Best Regards
/Magnus
"Scott Morris" <bogus@.bogus.com> wrote in message
news:uZhVjdhbGHA.3908@.TK2MSFTNGP02.phx.gbl...
> Please have a look at the syntax used to create functions. You are
> attempting to create an inline function which must not have a function
> body and which must contain a single select query within the returns
> clause. If you want to include logic that cannot be handled within a
> single select statement, then you will need to use the more complex
> table-valued function syntax.
>

Function with EXECUTE problem

Hello,

I'm running into some trouble writing a function that returns a table. I'm using OPENQUERY in the FROM clause to fetch data from a remote server (ORACLE). Because the query must be dynamic, I have to execute it using the EXECUTE command.

Basically, I first dynamically create a string containing my query and then I execute it by calling the EXECUTE function and passing my string as an argument.

Now, I'd like my function to return what's produced by the EXECUTE call. Here's how I wrote the function declaration:

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

CREATE FUNCTION [dbo].[beh_GetRemoteData] (@.STARTDATE varchar(20), @.POS int, @.ID_ANALOG varchar(10))
RETURNS @.RET TABLE (HIST_TIMESTAMP DATETIME, ID_ANALOG INT, STATUT INT, QUALITY INT, VALUE NUMERIC(12,5)) AS
BEGIN
DECLARE @.REMOTEQUERY varchar(300)
DECLARE @.LOCALQUERY varchar(400)

--======== Create remote Query =======--
SET @.REMOTEQUERY = 'SELECT * FROM [...]'


--======== Create local Query =======--

SET @.LOCALQUERY = 'SELECT [...] FROM OPENQUERY(REMOTE_SERVER, ''' + @.REMOTEQUERY + ''')'

INSERT INTO @.RET
EXEC(@.LOCALQUERY)

RETURN
END

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Of course, this doesn't even passes the "Check syntax" because the EXEC statement cannot be used as as source when inserting into a table variable. This might be but it's EXACTELY what I want to do.

Any ideas on how I should write my function?

Thanks,

Skip.According to my reading of books on line for SQL Server 2000 you can't execute anything other than an extended stored procedure inside a function anyway - and those can't return results sets. Therefore, you can't use dynamic sql either.

Function with CASE Statements

Hi,

Uses: SQL Server 2000 + Winxp PRO;

I Created a Function as below:

<CODE>
CREATE FUNCTION [GetDestinationOperator] (@.Dest VARCHAR(24))
RETURNS VARCHAR(15)
AS
BEGIN

DECLARE @.Op VARCHAR(15)

SET @.Dest = RTRIM(@.Dest)

CASE LEN(@.Dest)
WHEN 13 THEN SET @.Op = 'IDD'
WHEN 10 THEN
CASE SUBSTRING (@.Dest , 1 , 3 )
WHEN '071' THEN SET @.Op = 'MOBITEL'
WHEN '072' THEN SET @.Op = 'CELTEL'
WHEN '077' THEN SET @.Op = 'DIALOG'
WHEN '078' THEN SET @.Op = 'HUTCH'
ELSE SET @.Op = 'NATIONAL'
END
WHEN 7 THEN
CASE SUBSTRING (@.Dest , 1 , 1 )
WHEN '2' THEN SET @.Op = 'SLT'
WHEN '4' THEN SET @.Op = 'SUNTEL'
WHEN '5' THEN SET @.Op = 'LANKA BELL'
ELSE SET @.Op = 'LOCAL'
END
WHEN 3 THEN SET @.Op = 'INTERNAL'
ELSE SET @.Op = 'UNKNOWN'
END

RETRUN @.Op

END
<\CODE>

However when I checked the Code I written I get error Message as saying:

Error = 156:
Incorrect Syntax near the Keyword CASE
Incorrect Syntax near the Keyword WHEN
.
.
.

However when I executed the Query which I used with my database mentioned below, I get no errors:

<CODE>

SELECT CASE LEN(CalledNo)
WHEN 13 THEN 'IDD'
WHEN 10 THEN
CASE SUBSTRING (CalledNo , 1 , 3 )
WHEN '071' THEN 'MOBITEL'
WHEN '072' THEN 'CELTEL'
WHEN '077' THEN 'DIALOG'
WHEN '078' THEN 'HUTCH'
ELSE 'NATIONAL'
END
WHEN 7 THEN
CASE SUBSTRING (CalledNo , 1 , 1 )
WHEN '2' THEN 'SLT'
WHEN '4' THEN 'SUNTEL'
WHEN '5' THEN 'LANKA BELL'
ELSE 'LOCAL'
END
WHEN 3 THEN 'INTERNAL'
ELSE 'UNKNOWN'
END, CalledNo
FROM PABX
WHERE ExtNo = 204

<\CODE>

What might be the problem here?

Regards,

Hifni

Can you try this
CREATE FUNCTION GetDestinationOperator (@.Dest VARCHAR(24))
RETURNS VARCHAR(15)
AS
BEGIN

DECLARE @.Op VARCHAR(15)

SET @.Dest = RTRIM(@.Dest)
select @.op=
CASE LEN(@.Dest)
WHEN 13 THEN 'IDD'
WHEN 10 THEN
CASE SUBSTRING (@.Dest , 1 , 3 )
WHEN '071' THEN 'MOBITEL'
WHEN '072' THEN 'CELTEL'
WHEN '077' THEN 'DIALOG'
WHEN '078' THEN 'HUTCH'
ELSE 'NATIONAL'
END
WHEN 7 THEN
CASE SUBSTRING (@.Dest , 1 , 1 )
WHEN '2' THEN 'SLT'
WHEN '4' THEN 'SUNTEL'
WHEN '5' THEN 'LANKA BELL'
ELSE 'LOCAL'
END
WHEN 3 THEN 'INTERNAL'
ELSE 'UNKNOWN'
END

RETURN @.Op

END|||The case statement cannot stand alone even within a fuction. Create a variable called @.result and modify your fuction to Select @.result = <your case statement> then return the result.|||

Replace the CASE statment with a series of

IF condition

BEGIN

...

END

That should work

|||CASE is an expression not a control of flow statement. So you need to either assign the variable @.op using the value of the CASE expression or use IF...ELSE statements. Also, you should avoid writing scalar UDFs like this which perform lookup operations and use it in SELECT statements. You will get sub-optimal performance. It is based to model the lookup in a table and then perform a simple SELECT operation. This also gives added flexibility and performs better. Or you can inline the expression in the UDF in a view or TVF and use that instead.|||

Hi Everyone,
Thanks for all of your responses. For Eisa, I tried your sql and it did work wery well. Thanks for it and Umarchandra who gave a technical detail of it, thanks as well and for the rest.

Regards,

function with a Cellset as parameter?

Hi all,

I want to create a function (Analysis Servieces 2005) that expects a CellSet as Parameter, but I don't understand very good how to pass A Cellset to a function.
I have the next Code that should return my CellSet:

SELECT NON EMPTY{ [Measures].[InvoiceAmount] } ON COLUMNS, NON EMPTY { ([Buyer].[Company].[Company].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
( SELECT ( { [Com Device].[Com Device].["ComDevice"] } ) ON COLUMNS FROM
( SELECT ( { [Invoice].[Period Code].["PeriodeCode"] } ) ON COLUMNS FROM
( SELECT ( { [Seller].[Company].["Seller"] } ) ON COLUMNS FROM [Invoicing])))
WHERE ( [Seller].[Company].[Seller], [Invoice].[Period Code].["PeriodeCode"], [Com Device].[Com Device].["ComDevice"])

Any help is appriciated.

What sort of function do you mean?
Where would you want to call this function from?
What is this function meant to do?

|||

Hi Adam,

It is a .Net function, that should return a string as value.
the select query has 2 filter-values, I want the companies that had the "comdevice" in the given "PeriodeCode".
I am calling the function from reporting services.

Hope this clears it a bit.

thanx
PS.(Is it maby possible to excute the query within the function, It is a function within the db-assemblies?)

|||

Sorry, I'm still not clear as to why you want to do this. Why write a .NET function?

My understanding is that all you want is a parametrized MDX query. Is this correct? If so reporting services supports this.

Can you describe the problem you are trying to solve rather than the solution you are trying to come up with. Maybe there's an easier solution that can accomplish what you need.

|||

Thanx for your reply Adam,

I have the next problem:
I have a table in my report, that uses dataset "Comdevice". In that same table I show some Invoice-values per Comdevice.
What I want to do is show the company that bought this comdevice. I can't do this in the same dataset, because the company that bought the device is on "SALESLEVEL 300" and the invoice-values I show are on "SALESLEVEL 240". My function would be a temporary solution to this problem.
Sorry if the solution is very clear, but I'm very new to Mdx.

thanx in advance

|||I'm not sure what you mean by "SALESLEVEL 300" and "SALESLEVEL 240". To help me understand what you are trying to do could you please mock up the desired report layout in excel and then copy paste it into a post.

Function vs. Sub-Query

When my sproc selects a function (which in itself has a select statement to gather data) it takes substantially longer time (minutes) than if I replace the function with a sub query in the sproc (split second). What is the reason for this?
BjornI've seen this too. In my case when I looked at the execution plan and the server trace it appears the udf is called for each row returned where the sub query doesn't. I was using a udf to calculate the status of the records. I ended up using a view instead to calculate the status and joined my original query to the view. This is similiar to a sub query and much much faster than the udf.|||Thanks! It makes sense. It seems that the sub query runs first and only once in an sproc, extracting all the data needed for the main query.

Bjorn