Functions vs StoredProcedures

From a sp I should call many other sp or functions. There're difference
between SP and Functions about performance? Are functions more performant
then sp.
ThanksProcedures and Functions are not functionaly equivalent. For example,
functions can only perform inserts/updates/deletes on table variables that
it declares. Also, only a function can return a scalar value.
http://msdn.microsoft.com/library/d...edprocedure.asp
http://msdn.microsoft.com/library/d...br />
50mr.asp
"checcouno" <checcouno@.discussions.microsoft.com> wrote in message
news:D8456144-D913-4F39-AF15-F786C3155C18@.microsoft.com...
> From a sp I should call many other sp or functions. There're difference
> between SP and Functions about performance? Are functions more performant
> then sp.
> Thanks|||>> functions can only perform inserts/updates/deletes on table variables
Just to clarify. Functions can be table valued as well. And you can update
permanent tables in a database using a table valued function as well:
CREATE TABLE tbl ( col INT NOT NULL PRIMARY KEY );
GO
CREATE FUNCTION ufn ( @.p INT ) RETURNS TABLE AS
RETURN ( SELECT col FROM tbl WHERE col = @.p )
GO
INSERT ufn(1) SELECT 1 ; SELECT * FROM tbl ;
UPDATE ufn(1) SET col = 2 WHERE col = 1 ; SELECT * FROM tbl ;
DELETE ufn(2) WHERE col = 2 ; SELECT * FROM tbl ;
The restriction is that you cannot perform them on permanent tables from
within the function.
Anith|||FWIW: 'performant' isn't a word.
"checcouno" <checcouno@.discussions.microsoft.com> wrote in message
news:D8456144-D913-4F39-AF15-F786C3155C18@.microsoft.com...
> From a sp I should call many other sp or functions. There're difference
> between SP and Functions about performance? Are functions more performant
> then sp.
> Thanks|||On Wed, 28 Sep 2005 11:26:57 -0500, Anith Sen wrote:

>Just to clarify. Functions can be table valued as well. And you can update
>permanent tables in a database using a table valued function as well:
(snip)
Hi Anith,
After running your code, I can't deny that you _CAN_ do this. But this
is not mentioned anywhere in Books Online (in fact, BOL says that
INSERT, UPDATE, and DELETE all operate on a table, a view, or a
OPENQUERY or OPENROWSET rowset-function). I don't think that anyone
should ever rely on this behaviour!
(If I may speculate - I *think* that the reason for this behaviour is
that inline table-valued functions and views have so much in common that
they re-use lots of the same code, and someone at MS forgot to exclude
UDFs in the re-used code for INSERT, UPDATE and DELETE).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Actually it is a word, but perhaps used out of context:
http://dictionary.reference.com/search?q=performant
"Mark" <a@.b.net> wrote in message
news:ueozaHGxFHA.612@.TK2MSFTNGP10.phx.gbl...
> FWIW: 'performant' isn't a word.
>
> "checcouno" <checcouno@.discussions.microsoft.com> wrote in message
> news:D8456144-D913-4F39-AF15-F786C3155C18@.microsoft.com...
>|||I don't think that english is his mother tongue.
In French "performant" is an adjective that discribes something that has
performs well.
For the life of me I cannot think of an English word that can be used in the
same way.
My French-English translator doesn't even make a suggestion.
"JT" <someone@.microsoft.com> wrote in message
news:OQKvMtRxFHA.3756@.tk2msftngp13.phx.gbl...
> Actually it is a word, but perhaps used out of context:
> http://dictionary.reference.com/search?q=performant
> "Mark" <a@.b.net> wrote in message
> news:ueozaHGxFHA.612@.TK2MSFTNGP10.phx.gbl...
>|||>> After running your code, I can't deny that you _CAN_ do this. But this is
I tend to agree with the lack of sufficient documentation which can cause
some confusion. For instance, add a DISTINCT to the SELECT clause in the UDF
& you'll see the same limitations of updateable views.
Anith

Function to call function by name given as parameter

I want to write function to call another function which name is
parameter to first function. Other parameters should be passed to
called function.
If I call it function('f1',10) it should call f1(10). If I call it
function('f2',5) it should call f2(5).

So far i tried something like

CREATE FUNCTION [dbo].[func] (@.f varchar(50),@.m money)
RETURNS varchar(50) AS
BEGIN
return(select 'dbo.'+@.f+'('+convert(varchar(50),@.m)+')')
END

When I call it select dbo.formuła('f_test',1000) it returns
'select f_test(1000)', but not value of f_test(1000).

What's wrong?

MariuszMariusz (vd06@.o2.pl) writes:
> I want to write function to call another function which name is
> parameter to first function. Other parameters should be passed to
> called function.
> If I call it function('f1',10) it should call f1(10). If I call it
> function('f2',5) it should call f2(5).
> So far i tried something like
> CREATE FUNCTION [dbo].[func] (@.f varchar(50),@.m money)
> RETURNS varchar(50) AS
> BEGIN
> return(select 'dbo.'+@.f+'('+convert(varchar(50),@.m)+')')
> END
> When I call it select dbo.formuła('f_test',1000) it returns
> 'select f_test(1000)', but not value of f_test(1000).
> What's wrong?

Nothing. Or everything. Just take a step back, and put yourself in
the position of SQL Server. You tell SQL Server to evaluate a string
expression. How on Earth should SQL Server see that the result of this
expression is its turn also an expression that should be evaluated?

Had you been in a stored procedure, you could have used dynamic SQL. Now
you are in a function, and the only way to do this is:

IF @.f = 'that_func'
RETURN (dbo.that_func(@.f))
ELSE @.f = 'this_func'
RETURN (dbo.that_func(@.f))
etc

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> Had you been in a stored procedure, you could have used dynamic SQL. Now
> you are in a function, and the only way to do this is:
> IF @.f = 'that_func'
> RETURN (dbo.that_func(@.f))
> ELSE @.f = 'this_func'
> RETURN (dbo.that_func(@.f))
> etc
But I want to call this_func or that_func, or maybe a few other
functions without a need to modify wrapper function. Somehow I managed
to write stored procedure which does what I want:

CREATE PROCEDURE [dbo].[f] @.funkcja varchar(50), @.arg1 varchar(50),
@.koszt money OUTPUT
AS
BEGIN
declare @.cmd nvarchar(50)
declare @.par nvarchar(50)
set @.cmd=N'set @.koszt='+@.funkcja+'(@.arg)'
set @.par=N'@.koszt money output, @.arg varchar(50)'
execute sp_executesql @.cmd, @.par, @.koszt output, @.arg=@.arg1
END

Now I have onother problem: SPs cannot be used inside functions.
Only functions and extended SPs. Can I write extended SP to execute
SPs from functions?

Mariusz|||Mariusz (vd06@.o2.pl) writes:
> But I want to call this_func or that_func, or maybe a few other
> functions without a need to modify wrapper function. Somehow I managed
> to write stored procedure which does what I want:
> CREATE PROCEDURE [dbo].[f] @.funkcja varchar(50), @.arg1 varchar(50),
> @.koszt money OUTPUT
> AS
> BEGIN
> declare @.cmd nvarchar(50)
> declare @.par nvarchar(50)
> set @.cmd=N'set @.koszt='+@.funkcja+'(@.arg)'
> set @.par=N'@.koszt money output, @.arg varchar(50)'
> execute sp_executesql @.cmd, @.par, @.koszt output, @.arg=@.arg1
> END
> Now I have onother problem: SPs cannot be used inside functions.
> Only functions and extended SPs. Can I write extended SP to execute
> SPs from functions?

Yes, but in such case why call the stored procedure? Why not call the
function from the external stored procedure directly if you really want
to take this road. And I think it would be a very very bad road to take.
There are tons of reasons why you should not go there.

It might be that you already have external stored procedures in the
system (for better reasons than this one), but if you have not, you
have created a deplyoment problem. There is one more component that should
be deployed in production.

And extended stored procedures always incur a risk. An access violation
does not only crash your stored procedure - the whole SQL Server process
is blown away.

Furthermore, apparently this is a scalar function. If you say:

SELECT dbo.f(@.funkcja, @.arg1) FROM tbl

and you call an extended stored proc for each row in tbl, how effeciently
do you that will be?

Rewrite your functions to stored procedures that work on a temptable or
a spid-keyed table where it receives input parameters and return data. You
need a dispatch procedure, as you can say:

EXEC @.sp @.arg1

Where @.sp is the name of your procedure. (The above works for scalar-
values UDF:s also, by the way.)

For more information about sharing data over temp-tables, please see
http://www.sommarskog.se/share_data.html#temptables.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Function sequence error with bcp call from a stored procedure

I have a stored procedure that contains a series of bcp calls to export data
.
The bcp calls are executed by xp_cmdshell and are all the same (other than
the table name of course).
I have SET NOCOUNT ON as the first line in the stored procedure and it is
not returning any data. The bcp calls are in the format of:
bcp "select * from DBName..TableName WHERE UID = 'SomeUniqueNumber' queryout
SomePath\Tablename.txt -m0 -e SomePath\TableName.err -c -T -k'
exec @.RC = master..xp_cmdshell @.Query
On one of the bcp shells I'm receiving the following errors but not on
others. The errors are:
SQLState = S1010, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Function sequence error
NULL
Server: Msg 60003, Level 11, State 1, Procedure ExportData, Line 98
[Microsoft][ODBC SQL Server Driver][SQL Server]Data export operation failed.
Failed to get the call stack!
ODBC: Msg 0, Level 19, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler:
Process 64 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQ
L
Server is terminating this process.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).
Server: Msg 11, Level 16, State 1, Line 0
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check
your network documentation.
@.RETURN_VALUE = N/A
The stored procedure is being called from a .Net class library and is
executing on SQL Server 2000 with following version info:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
Any ideas would be appreciated. Thanks.AV normally means a bug in sqlserver code. I would suggest you contact PSS
regarding this.
-oj
"jacob4408" <jacob4408@.discussions.microsoft.com> wrote in message
news:6C3D821F-14E3-42BA-AC45-891C24D83451@.microsoft.com...
>I have a stored procedure that contains a series of bcp calls to export
>data.
> The bcp calls are executed by xp_cmdshell and are all the same (other than
> the table name of course).
> I have SET NOCOUNT ON as the first line in the stored procedure and it is
> not returning any data. The bcp calls are in the format of:
> bcp "select * from DBName..TableName WHERE UID = 'SomeUniqueNumber'
> queryout
> SomePath\Tablename.txt -m0 -e SomePath\TableName.err -c -T -k'
> exec @.RC = master..xp_cmdshell @.Query
>
> On one of the bcp shells I'm receiving the following errors but not on
> others. The errors are:
> SQLState = S1010, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Function sequence error
> NULL
> Server: Msg 60003, Level 11, State 1, Procedure ExportData, Line 98
> [Microsoft][ODBC SQL Server Driver][SQL Server]Data export operation
> failed.
> Failed to get the call stack!
>
> ODBC: Msg 0, Level 19, State 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler:
> Process 64 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION.
> SQL
> Server is terminating this process.
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).
> Server: Msg 11, Level 16, State 1, Line 0
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check
> your network documentation.
> @.RETURN_VALUE = N/A
> The stored procedure is being called from a .Net class library and is
> executing on SQL Server 2000 with following version info:
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
> Any ideas would be appreciated. Thanks.
>|||AV? PSS? Sorry, but I'm not familiar with the acronyms. Could you elaborat
e?
"oj" wrote:

> AV normally means a bug in sqlserver code. I would suggest you contact PSS
> regarding this.
> --
> -oj
>
> "jacob4408" <jacob4408@.discussions.microsoft.com> wrote in message
> news:6C3D821F-14E3-42BA-AC45-891C24D83451@.microsoft.com...
>
>|||Jacob,
Sorry. I mean Access Violation and Product Support Services.
http://support.microsoft.com/oas/de...aspx?gprid=2852
-oj
"jacob4408" <jacob4408@.discussions.microsoft.com> wrote in message
news:3DF8F91A-D0B8-46AE-9946-AF8EC9D87CFF@.microsoft.com...
> AV? PSS? Sorry, but I'm not familiar with the acronyms. Could you
> elaborate?
> "oj" wrote:
>|||Thanks, I'll look into that.
"oj" wrote:

> Jacob,
> Sorry. I mean Access Violation and Product Support Services.
> http://support.microsoft.com/oas/de...aspx?gprid=2852
> --
> -oj
>
> "jacob4408" <jacob4408@.discussions.microsoft.com> wrote in message
> news:3DF8F91A-D0B8-46AE-9946-AF8EC9D87CFF@.microsoft.com...
>
>

Function can be used in one server and not another

I have created a Function and I can call it in one server and i created
it in another server in my database and I get the error that object
does not exists in master.dbo
how do i get this funtion to work
i can see the function in the list.Hi,
You can use four part naming covention to call object from diffrent server
i.e. server.database.owner.objet
But before this you need to add that server as linked server using
sp_addlinkserver
vishal.sql@.gmail.com wrote:
>I have created a Function and I can call it in one server and i created
>it in another server in my database and I get the error that object
>does not exists in master.dbo
>how do i get this funtion to work
>i can see the function in the list.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1

Function can be used in one server and not another

I have created a Function and I can call it in one server and i created
it in another server in my database and I get the error that object
does not exists in master.dbo
how do i get this funtion to work
i can see the function in the list.Hi,
You can use four part naming covention to call object from diffrent server
i.e. server.database.owner.objet
But before this you need to add that server as linked server using
sp_addlinkserver
vishal.sql@.gmail.com wrote:
>I have created a Function and I can call it in one server and i created
>it in another server in my database and I get the error that object
>does not exists in master.dbo
>how do i get this funtion to work
>i can see the function in the list.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1

function call with table as parameter

Hi all, I want to use a function with a tabel object as parameter. Does
someone know a method to do this. I have read that a table as parameter
is invalid.Tables are accessible to functions as well as to calling code so it
shouldn't be necessary to pass them as parameters. It sounds like you
are trying to abstract some logic in a function that might be better
done some other way.

Please explain more fully the problem you are having.

--
David Portas
SQL Server MVP
--|||I should have said that PERMANENT tables are accessible to functions as
well as to calling code. Temporary tables are not. Are you trying to
access a temporary table within a function? If so, consider using an SP
instead.

--
David Portas
SQL Server MVP
--|||I am writing a SP and my idea was to call a function that does some
substitutions at a template-text. The substitution values and original
values should be posted by a dynamic generated table at the SP.

But perhaps it is realy the best way to do the substitutions direct
inside the SP.|||Thomas (Thomas.Martin@.eu.necel.com) writes:
> Hi all, I want to use a function with a tabel object as parameter. Does
> someone know a method to do this. I have read that a table as parameter
> is invalid.

This article of mine may give you some inspiration:
http://www.sommarskog.se/share_data.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Function call to Progress linked server

I have a Progress DB set up as a linked server.

To get the data through to SQL Server 2005 in a useable format i need to use the progress PRO_ELEMENT function call. How do I delimit this so it gets passed to the progress DB.

I've tried

SELECT

{fn PRO_ELEMENT(fldarr1,1,1)} as fld1

from ls1..pub.tab

This just returns an unknown function message which I believe is on the SQL Server end of the call.

This statement works fine through Business Objects.

Any help greatfully received.

try using OPENQUERY function.

select * from openquery (lnkd_server, 'SELECT {fn PRO_ELEMENT(fldarr1,1,1)} as fld1 from ls1..pub.tab')

Function call in Insert Statment

Hi

i m trying to call a function in insert statment

Insert Into (value, value1)

Value(@.value, dbo.function(@.value1)

dbo.function returns a value,

when i test the function in querry builder all goes fine.

In my program i become a error

"Parameterized Query '' ' expects parameter @.value1 , which was not supplied."

I m using visual studio , tableadapter.update function to insert datarecords in db

thx for help

Hi,

seems that you only provided 1 paramters within your query statement / parameter collection. The statement expects 2 value / value1 which both have to be supplied, if this is the same paramter you can just use the same name for them

Insert Into (value, value1)

Value(@.value, dbo.function(@.value)) --> There was also a closing parant. missing

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||supply a default value for parameter @.value in the front end. Just in case the function would not return one.

Function call in Dataset Query

Hello Guys,

I have a question that seems easy but I can not figure out...

Premise:

Have Custom code that fixes Divide by Zero Errors in SSRS. I have added the code to the Custom Code area in Report Properties correctly.

I have a Dataset that has a calculation for a column within a select statement

Query Pseudocode:

select ...[FRC%]=convert(decimal(13,2),sum(cost))/convert(decimal(13,2),sum(income))...
,year
from

(subquery"blah" )

Union

(Subquery"blah")

Custom Code:

Public Function SafeDiv(ByVal numerator as Double, ByVal denominator as Double) as Double
if denominator = 0 then
return 0
else
return numerator/denominator
end if
End Function

How To use:

If you have a field that does division and you need to eliminate the divide by zero error that occurs with SSRS then type =code.SafeDiv(first,second) in the field.

Problem:

How do I add this code reference in the following dataset select statement

select ...[FRC%]=convert(decimal(13,2),sum(cost))/convert(decimal(13,2),sum(income))...
,year
from

(subquery"blah" )

Union

(Subquery"blah") table1

---

I tried to do this:

from this:

[FRC%]=convert(decimal(13,2),sum(cost))/convert(decimal(13,2),sum(income)) ...

to this

[FRC%]=code.Safediv(convert(decimal(13,2),sum(cost)),convert(decimal(13,2),sum(income))) ...

---

But it did not work...gave me this error:

TITLE: Microsoft Report Designer

An error occurred while executing the query.
Cannot find either column "code" or the user-defined function or aggregate "code.safediv", or the name is ambiguous.

ADDITIONAL INFORMATION:

Cannot find either column "code" or the user-defined function or aggregate "code.safediv", or the name is ambiguous. (Microsoft SQL Server, Error: 4121)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=4121&LinkId=20476

BUTTONS:

OK

---

Help!

P.S.

this is a Matrix report and this select statement is within one of the datasets that fill a matrix.

anyone...?|||

Hello,

Unfortunately, you can't use custom code in your SQL query (as you've found). What you can do is supply both fields in the calculation (cost and income) to the report and have it do the percentage, or create a 'SafeDiv' function in SQL and do it there.

Hope this helps.

Jarret

|||

On the DataSet

Use Generic Query Designer

Then you can use

="Select tableName.ProductID, "& code.Safediv(Parameters) & " as ColumnName

From tableName"

Try to adapts it to your report.

I hope it help you.

|||Thanks I will try it...|||

I think this will work...I will reply with result...

Thank You!

function call acting odd

We upgraded to SP4 last night. Prior to that upgrade, a specific function
worked fine (since 2003 as a matter of fact). Now, if I create a view using
Enterprise Manager and call this function, I get an erroneous error message
about needing to convert a data type. If I write the same query in query
analzyer, however, it works as expected. Prior views and stored procedures
that call this function continue to work as expected. Any suggestions on
what is going on here? Is this SP4 related or something else?> create a view using Enterprise Manager
I strongly recommend sticking with Query Analyzer for this kind of task. EM
is fine for look-see and administrative type tasks, but I do not think it is
the optimal tool for script or data management.
A|||Aaron,
Query Analyzer may be the better choice but the point is that EM no longer
works as it did in the past, which tells me there is a problem somewhere.
Thanks anyway.
"Aaron Bertrand [SQL Server MVP]" wrote:

> I strongly recommend sticking with Query Analyzer for this kind of task.
EM
> is fine for look-see and administrative type tasks, but I do not think it
is
> the optimal tool for script or data management.
> A
>
>|||> Query Analyzer may be the better choice but the point is that EM no longer
> works as it did in the past, which tells me there is a problem somewhere.
And if you service your Yugo, its behavior may change also. Doesn't mean
you should have ever had a Yugo in the first place. :-)

Function Call - Syntax error

Hi All,
I have defined a function and want to call the same in a stored procedure.
The function call is giving me syntax error! Here is the function call.
function_name(@.param)
What is the correct syntax to call the function?
kdkd
SELECT * FROM dbo.yourfunction('param')
OR
SELECT dbo.Yourfunction('param')
"kd" <kd@.discussions.microsoft.com> wrote in message
news:F46EFF26-10A3-49CF-BF2E-5A5D6C31DE42@.microsoft.com...
> Hi All,
> I have defined a function and want to call the same in a stored procedure.
> The function call is giving me syntax error! Here is the function call.
> function_name(@.param)
> What is the correct syntax to call the function?
> kd|||Here is another example for you.
create function dbo.testf()
returns int
as
begin
return(33)
end
go
print dbo.testf()
go
drop function dbo.testf
HTH, Thanks
ZULFIQAR SYED
"kd" wrote:

> Hi All,
> I have defined a function and want to call the same in a stored procedure.
> The function call is giving me syntax error! Here is the function call.
> function_name(@.param)
> What is the correct syntax to call the function?
> kd|||You have to preface the functionname with "dbo.", as in
dbo.function_name(@.param)
If the function returns a scalar value (integer, float, varchar() etc.. Then
just usethe expression anywhere you would use the intrinsic datatype using
the same syntax
Select dbo.function_name(@.param), ColA, ColB
from table ...
or if it returns a table then use it exactly as you would a table...
Select <stuff> From dbo.function_name(@.param)
or...
Select <stuff>
From Table As T
Join dbo.function_name(@.param) As F
On F.ColA = T.ColA
"kd" wrote:

> Hi All,
> I have defined a function and want to call the same in a stored procedure.
> The function call is giving me syntax error! Here is the function call.
> function_name(@.param)
> What is the correct syntax to call the function?
> kd