How to 'call' UDFs from SPs

New to SQL Server in general (2000 in particular) and working through the
relative difficulty of upsizing an Access '03 database that's currently
used in a small ASP web application. The database is generally hit via the
ADO command object, passing parameters as needed via CommandText.
Could someone show an example or two of how to 'call' a user-defined
function (with one parameter) from a stored procedure (also with one input
parameter, the same parameter that's used in the function), where the
procedure relies on a value returned by the function?
Please excuse if this is entirely too unclear, or is posted to the wrong
section. Thanks.
Message posted via http://www.droptable.comWithout knowing exactly what you're trying to do, you would call a UDF that
returns an integer as follows:
DECLARE @.i INTEGER
SET @.i = dbo.udfname(parameter)
Your stored procedure could make this call and use the variable @.i in
whatever fashion it needs. Not sure entirely what you're trying to do, or
how you're trying to do it, or the types you're dealing with, but that's the
basics of it.
Now if you're talking about the mechanics of the call from the ASP web app
side, we need to look at other stuff.
"The Gekkster via droptable.com" <forum@.droptable.com> wrote in message
news:17b4719d63284899b65bdc6083ed0ef8@.SQ
droptable.com...
> New to SQL Server in general (2000 in particular) and working through the
> relative difficulty of upsizing an Access '03 database that's currently
> used in a small ASP web application. The database is generally hit via the
> ADO command object, passing parameters as needed via CommandText.
> Could someone show an example or two of how to 'call' a user-defined
> function (with one parameter) from a stored procedure (also with one input
> parameter, the same parameter that's used in the function), where the
> procedure relies on a value returned by the function?
> Please excuse if this is entirely too unclear, or is posted to the wrong
> section. Thanks.
> --
> Message posted via http://www.droptable.com|||For stored procedures, it is not "necessary," although preferable, to
qualify the object with the owner. For UDFs, however, this is mandatory:
<owner/schema>.<UDF name><parameter list>
SET @.var = dbo.udfname(@.parm1, @.parm2, ..., @.parmn)
Sincerely,
Anthony Thomas
"Michael C#" <xyz@.abcdef.com> wrote in message
news:h2K0e.402$CN3.123@.fe11.lga...
Without knowing exactly what you're trying to do, you would call a UDF that
returns an integer as follows:
DECLARE @.i INTEGER
SET @.i = dbo.udfname(parameter)
Your stored procedure could make this call and use the variable @.i in
whatever fashion it needs. Not sure entirely what you're trying to do, or
how you're trying to do it, or the types you're dealing with, but that's the
basics of it.
Now if you're talking about the mechanics of the call from the ASP web app
side, we need to look at other stuff.
"The Gekkster via droptable.com" <forum@.droptable.com> wrote in message
news:17b4719d63284899b65bdc6083ed0ef8@.SQ
droptable.com...
> New to SQL Server in general (2000 in particular) and working through the
> relative difficulty of upsizing an Access '03 database that's currently
> used in a small ASP web application. The database is generally hit via the
> ADO command object, passing parameters as needed via CommandText.
> Could someone show an example or two of how to 'call' a user-defined
> function (with one parameter) from a stored procedure (also with one input
> parameter, the same parameter that's used in the function), where the
> procedure relies on a value returned by the function?
> Please excuse if this is entirely too unclear, or is posted to the wrong
> section. Thanks.
> --
> Message posted via http://www.droptable.com|||> "Michael C#" <xyz@.abcdef.com> wrote in message
> news:h2K0e.402$CN3.123@.fe11.lga...
...
> SET @.i = dbo.udfname(parameter)|||Thanks, guys.
I was trying to do something like this, although UDFs and SPs are new to me
and I'm obviously missing something here:
*******
CREATE PROCEDURE dbo.usp_My_Procedure
(
DECLARE @.CountOfWidgets INTEGER
SET @.CountOfWidgets = dbo.udf_My_Function(@.B)
)
AS
DECLARE @.B varchar(255)
SELECT COUNT(CountOfWidgets ) AS CountOfCountOfWidgets,
CountOfCountOfWidgets / CountOfWidgets AS Expr2
FROM dbo.udf_My_Function(@.B) CROSS JOIN
dbo.some_other_***
dbo.some_other_***
WHERE dbo.udf_My_Function(@.B)) AND some_other_criteria_***
GROUP BY CountOfWidgets, something_here_***
*******
In this scenario, @.B is a parameter that is passed (or needs to be passed)
from the ASP script to the procedure, like this:
*******
objCommand.CommandText = "usp_My_Procedure '" & objRS("WidgetBrand") & "'"
objCommand.CommandType = adCmdStoredProc
set objRS = objCommand.Execute
*******
The ASP part has been working fine when using Access; I'm just not yet up
to speed with procedures and functions for SQL Server.
Ideas...?
Message posted via http://www.droptable.com|||I think I see some potential "issues" in your SELECT statement already (I'm
assuming the ***'s are actually just where you blanked out the names for our
benefit of course). It would definitely help narrow down the problem if you
could post DDL, sample data and expected results. Your UDF code would help
troubleshoot it also.
You can try running your usp_My_Procedure in QA to see if it actually works
the way you expect it to also, or to see what error it returns if not.
Thanks
"The Gekkster via droptable.com" <forum@.droptable.com> wrote in message
news:316caf07f5ff49419d92cc87abdd0b7e@.SQ
droptable.com...
> Thanks, guys.
> I was trying to do something like this, although UDFs and SPs are new to
> me
> and I'm obviously missing something here:
> *******
> CREATE PROCEDURE dbo.usp_My_Procedure
> (
> DECLARE @.CountOfWidgets INTEGER
> SET @.CountOfWidgets = dbo.udf_My_Function(@.B)
> )
> AS
> DECLARE @.B varchar(255)
> SELECT COUNT(CountOfWidgets ) AS CountOfCountOfWidgets,
> CountOfCountOfWidgets / CountOfWidgets AS Expr2
> FROM dbo.udf_My_Function(@.B) CROSS JOIN
> dbo.some_other_***
> dbo.some_other_***
> WHERE dbo.udf_My_Function(@.B)) AND some_other_criteria_***
> GROUP BY CountOfWidgets, something_here_***
> *******
> In this scenario, @.B is a parameter that is passed (or needs to be passed)
> from the ASP script to the procedure, like this:
> *******
> objCommand.CommandText = "usp_My_Procedure '" & objRS("WidgetBrand") & "'"
> objCommand.CommandType = adCmdStoredProc
> set objRS = objCommand.Execute
> *******
> The ASP part has been working fine when using Access; I'm just not yet up
> to speed with procedures and functions for SQL Server.
> Ideas...?
> --
> Message posted via http://www.droptable.com|||Here is the (upsized) UDF:
*****
ALTER FUNCTION dbo.udf_EDB_Current_Inventory
(@.B varchar(255))
RETURNS TABLE
AS
RETURN ( SELECT COUNT(dbo.tblUnits.UnitInDatabase) AS
CountOfUnitInDatabase
FROM dbo.SQUISH_V2 CROSS JOIN
dbo.tblRetailers CROSS JOIN
dbo.tblUnits
WHERE (dbo.SQUISH_V2.CLASS_TYPE = @.B)
GROUP BY dbo.tblUnits.CurrentlyInStock, dbo.tblRetailers.Active
HAVING (dbo.tblUnits.CurrentlyInStock = 1) AND
(dbo.tblRetailers.Active = 1) )
*****
And this is the (upsized) SQL statement that needs to be incorporated into
the procedure, at least as 'determined/attempted' in the temporary Access
project (the cross joins are also courtesy of upsizing, and I'll need to
sort those out as well):
*****
SELECT COUNT(udf_EDB_Current_Inventory.CountOfUnitInDatabase) AS
CountOfCountOfUnitInDatabase,
CountOfCountOfUnitInDatabase /
udf_EDB_Current_Inventory.CountOfUnitInDatabase AS Expr2
FROM dbo.udf_EDB_Current_Inventory() udf_EDB_Current_Inventory
CROSS JOIN
dbo.SQUISH_V2 CROSS JOIN
dbo.tblRetailers CROSS JOIN
dbo.tblUnits
WHERE (dbo.SQUISH_V2.CLASS_TYPE = @.B) AND (DATEDIFF(d,
dbo.tblUnits.AddedToInventory, GETDATE()) > 30) AND (DATEDIFF(d,
dbo.tblUnits.AddedToInventory, GETDATE()) <= 60)
GROUP BY udf_EDB_Current_Inventory.CountOfUnitInDatabase,
dbo.tblUnits.CurrentlyInStock, dbo.tblRetailers.Active
HAVING (dbo.tblUnits.CurrentlyInStock = 1) AND
(dbo.tblRetailers.Active = 1)
*****
In trying to save/create the procedure I get the following error: "ADO
error: An insufficient number of arguments were supplied for the procedure
or function dbo.udf_EDB_Current_Inventory."
I'm not familiar enough with this, other than the error message is telling
me that either (i) the needed input parameter is not being passed; or (ii)
the function needs to include an output parameter to send back to the
procedure that's calling it?
Prior to this attempt to upsize, all data resided in SQL tables, and
queries were in Access (linked SQL tables). My hope was just to initially
move the queries into SQL Server and to take Access out of the picture. Too
bad I can't just use views with parameters, and worry about the greater
benefit of UDFs and SPs later. Or is that somehow possible...?
Message posted via http://www.droptable.com|||Sorry, Mike; I wasn't trying to correct what you said, but clarify a general
error most users have when they first start writing these. You had the
information there, but sometimes they need it spelled out.
Sincerely,
Anthony Thomas
"Michael C#" <xyz@.abcdef.com> wrote in message
news:UVL0e.8937$Qi6.1322@.fe09.lga...
> "Michael C#" <xyz@.abcdef.com> wrote in message
> news:h2K0e.402$CN3.123@.fe11.lga...
...
> SET @.i = dbo.udfname(parameter)|||On Fri, 25 Mar 2005 06:20:16 GMT, The Gekkster via droptable.com wrote:
(snip)
>In trying to save/create the procedure I get the following error: "ADO
>error: An insufficient number of arguments were supplied for the procedure
>or function dbo.udf_EDB_Current_Inventory."
Hi Gekkster,
From this part of the code you posted:

>ALTER FUNCTION dbo.udf_EDB_Current_Inventory
>(@.B varchar(255))
>RETURNS TABLE
I see that your user-defined function requires one argument.
In the stored procedure where you call the function, the call looks like
this:

>FROM dbo.udf_EDB_Current_Inventory() udf_EDB_Current_Inventory
And between the parentheses, there is no argument. The error message is
right: an insufficient number of arguments were supplied. Since I have
no idea what you are trying to accomplish, I can't give any suggestions
WHAT to put between the parentheses, but you better put SOMETHING there!
Oh, by the way:

>SELECT COUNT(udf_EDB_Current_Inventory.CountOfUnitInDatabase) AS
>CountOfCountOfUnitInDatabase,
> CountOfCountOfUnitInDatabase /
>udf_EDB_Current_Inventory.CountOfUnitInDatabase AS Expr2
is illegal in SQL Server. The whole SELECT statement is executed at
once; the alias CountOfCountOfUnitInDatabase can't be used in other
parts of the SELECT clause. The only place in a query where a column
alias can be used in in the ORDER BY clause.
The workaround is to either repeat the expression, or use a derived
table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo,
Thanks for the input. I've tried to rework this a bit, and now have the
following view and function (I've dropped some of the WHEREs for now to
help sort this out):
*******
SELECT dbo.tblUnits.UnitInDatabase, dbo.tblRetailers.Active,
dbo.tblUnits.CurrentlyInStock, dbo.SQUISH_V2.CLASS_TYPE,
dbo.Units.AddedToInventory, dbo.tblRetailers.Name
FROM dbo.SQUISH_V2 INNER JOIN
dbo.tblUnits ON dbo.SQUISH_V2.UnitID =
dbo.tblUnits.UnitID INNER JOIN
dbo.tblRetailers ON dbo.tblRetailers.RetailerID =
dbo.tblUnits.RetailerID
*******
*******
ALTER FUNCTION dbo.udf_EDB_Current_Inventory (
@.ClassTypeRequested varchar(255))
RETURNS TABLE
AS
RETURN (SELECT COUNT(UnitInDatabase) AS CountOfUnitInDatabase
FROM vw_EDB_Current_InventoryView
WHERE CLASS_TYPE = @.ClassTypeRequested
AND Active = 1
AND CurrentlyInStock = 1)
*******
So I can now retrieve the needed count via the function, which is properly
'restrictive' based on the WHERE clause it contains. When I test this in
SQL Server QA I get the correct result, like so:
DECLARE @.B varchar(255)
SET @.B = 'Custom'
SELECT *
FROM dbo.udf_EDB_Current_Inventory(@.B)
So my question now is this: To minimize (or hopefully avoid) having to
recode the ASP I'm using, is there a way to do this via a stored procedure
(or even the above function) so that I can continue to make the call like
this:
*******
objCommand1.CommandText = "usp_My_Procedure '" & objRS("SomeID") & "'"
objCommand1.CommandType = adCmdStoredProc
set objRS1 = objCommand1.Execute
*******
This part is still giving me some difficulty, as I don't seem to be getting
the 'parameter' passed properly as was the case with using Access.
Sorry for being so slow to get this - I can only plead ignorance from being
new to UDFs and SPs. Thanks.
Message posted via http://www.droptable.com