Question about referencing UDFs

OK - I've found pieces of the answer to this question in various places, but
never a complete one.
When referencing UDFs from TSQL, I know that table functions do not require
a "dbo." prefix (or any prefix for that matter), and that scalar functions d
o
require the prefix.
So what I want to know is, is it possible to use the "user" function (that
returns the current user) as the prefix instead of having to actually hard
code the user name? i.e. why can't I just do something like:
select user.myfunc()
instead of having to say:
select kingd.myfunc()
Is there another way to specify the current user?
Thanks in advance.
DKAre you saying you have different functions for every user, with the same
name? Yikes. Why not pass the USER_NAME() into the function and have the
logic there, instead of having to maintain an object per user? Who is going
to maintain these functions as users are added/removed?
"Dwayne King" <dwayne.king@.cognos.com> wrote in message
news:D905030B-4E3F-4543-93A4-E0EE1B95DC74@.microsoft.com...
> OK - I've found pieces of the answer to this question in various places,
> but
> never a complete one.
> When referencing UDFs from TSQL, I know that table functions do not
> require
> a "dbo." prefix (or any prefix for that matter), and that scalar functions
> do
> require the prefix.
> So what I want to know is, is it possible to use the "user" function (that
> returns the current user) as the prefix instead of having to actually hard
> code the user name? i.e. why can't I just do something like:
> select user.myfunc()
> instead of having to say:
> select kingd.myfunc()
> Is there another way to specify the current user?
> Thanks in advance.
>
> --
> DK|||Dwayne King,
It is not the "dbo" prefix, neither the user name. it is the owner of the
object.
What do think will happen when a user, that is not the function owner,
executes the statement?
SQL Server will look for user_no_owner.myfunc() and this will yield an error
.
AMB
"Dwayne King" wrote:

> OK - I've found pieces of the answer to this question in various places, b
ut
> never a complete one.
> When referencing UDFs from TSQL, I know that table functions do not requir
e
> a "dbo." prefix (or any prefix for that matter), and that scalar functions
do
> require the prefix.
> So what I want to know is, is it possible to use the "user" function (that
> returns the current user) as the prefix instead of having to actually hard
> code the user name? i.e. why can't I just do something like:
> select user.myfunc()
> instead of having to say:
> select kingd.myfunc()
> Is there another way to specify the current user?
> Thanks in advance.
>
> --
> DK|||Sorry - I haven't explained myself very well.
There is only one user and one set of functions. The problem is, this is a
product that our customer installs, and we allow them to choose what user to
install the product in at runtime. Therefore, we do not know during
development what the username will be. So I when developing our stored proc
s
we won't know how to prefix the function calls.
Does that clarify this at all?
DK
"Aaron Bertrand [SQL Server MVP]" wrote:

> Are you saying you have different functions for every user, with the same
> name? Yikes. Why not pass the USER_NAME() into the function and have the
> logic there, instead of having to maintain an object per user? Who is goi
ng
> to maintain these functions as users are added/removed?
>
> "Dwayne King" <dwayne.king@.cognos.com> wrote in message
> news:D905030B-4E3F-4543-93A4-E0EE1B95DC74@.microsoft.com...
>
>|||> Sorry - I haven't explained myself very well.
> There is only one user and one set of functions. The problem is, this is
> a
> product that our customer installs, and we allow them to choose what user
> to
> install the product in at runtime. Therefore, we do not know during
> development what the username will be. So I when developing our stored
> procs
> we won't know how to prefix the function calls.
> Does that clarify this at all?
Yes, that you are still confusing users and object owners.
My recommendation is to create all tables, procedures and functions with the
dbo. prefix, and to always use that prefix in the code.
A|||I'm more than willing to admit my ignorance of the difference. Most of my
experience is on Oracle :)
If we followed your suggestion of creating everything using the "dbo"
prefix, wouldn't the user be required to be the "dbowner"? The motivation
by mgmt was to allow the customer to install the product with a few
privileges as possible.
Sorry if I seem to be missing the point, but the differences in the SQL
Server concepts of login vs users never really made a lot of sense to me.
Thanks for your patience.
DK
"Aaron Bertrand [SQL Server MVP]" wrote:

> Yes, that you are still confusing users and object owners.
> My recommendation is to create all tables, procedures and functions with t
he
> dbo. prefix, and to always use that prefix in the code.
>|||> If we followed your suggestion of creating everything using the "dbo"
> prefix, wouldn't the user be required to be the "dbowner"?
NO. You need to grant users the right to execute stored procedures, etc.
The owner is not the only person who can see or use it.
This is a fairly common practice, and I see very few SQL 2000 installations
with even a single object owned by anyone but the explicit dbo.

> Sorry if I seem to be missing the point, but the differences in the SQL
> Server concepts of login vs users never really made a lot of sense to me.
Do you have Books Online? It may not be very exciting reading, but the
differences are laid out there.
A|||Wow.....nothing more humbling that learning a new database and it's
peculiarities :) Thanks for your patience.
I'm convinced there's some fundamental piece of information I'm still
missing. With the following trivial test case:
create table dbo.my_dbo_table (col1 varchar(10))
Neither of the following work because I'm missing SELECT privileges:
select * from my_dbo_table
select * from dbo.my_dbo_table
So I try:
grant select, insert, update,delete on dbo.my_dbo_table to jdbcuser
But that doesn't work, because I get "Grantor does not have GRANT
permission." So.......I'm allowed to create objects with dbo. but I then
retain no privileges on them, even though I created them?
Is the dbowner the only one allowed to grant privs on these objects?
DK
"Aaron Bertrand [SQL Server MVP]" wrote:

> NO. You need to grant users the right to execute stored procedures, etc.
> The owner is not the only person who can see or use it.
> This is a fairly common practice, and I see very few SQL 2000 installation
s
> with even a single object owned by anyone but the explicit dbo.
>
> Do you have Books Online? It may not be very exciting reading, but the
> differences are laid out there.|||> grant select, insert, update,delete on dbo.my_dbo_table to jdbcuser
> But that doesn't work, because I get "Grantor does not have GRANT
> permission." So.......I'm allowed to create objects with dbo. but I
> then
> retain no privileges on them, even though I created them?
> Is the dbowner the only one allowed to grant privs on these objects?
From the Books Online:
<Excerpt href="http://links.10026.com/?link=tsqlref.chm::/ts_ga-gz_8odw.htm">
The members of the symin role can grant any permissions in any database.
Object owners can grant permissions for the objects they own. Members of the
db_owner or db_securityadmin roles can grant any permissions on any
statement or object in their database.
</Excerpt>
I assume you are getting the error because none of the above apply. Since
you are able to create a dbo-owned object but not access it, it appears you
are a member of the db_ddladmin fixed database role. Members of that role
can create objects in any schema but that role membership doesn't
necessarily allow you to access or grant permissions on the created objects.
You won't run into this problem if you are also a member of the
db_securityadmin role but you might find it easier to run DDL scripts when
logged in as a symin role member or logged in as the database owner. In
both of these cases, your database security context will be the 'dbo' user
so all objects will be owned by 'dbo' by default. Alternatively, you can
run DDL as a db_owner role member but you will need to explicitly specify
'dbo' as the owner in order to create dbo-owned objects.
To add to what Aaron said, most SQL Server installations use dbo exclusively
for object ownership. This is because one can easily segregate dbo-owned
objects both logically and physically in the same SQL Server instance by
creating objects in different databases. 'dbo' will be used as the default
schema (when no like-named object is owned by the current user) so one
doesn't need to owner-qualify objects, except in the special case of UDFs.
although it is still a Best Practice to always owner-qualify objects.
It is probably best to stick with dbo-ownership if the target database is
dedicated to your application. BTW, the next version of SQL Server provides
a more clear distinction between owner and schema. I expect the dbo
ownership practice will lessen in SQL 2005.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dwayne King" <dwayne.king@.cognos.com> wrote in message
news:9D159350-E5AB-4CA7-88BF-80FA427E3936@.microsoft.com...
> Wow.....nothing more humbling that learning a new database and it's
> peculiarities :) Thanks for your patience.
> I'm convinced there's some fundamental piece of information I'm still
> missing. With the following trivial test case:
> create table dbo.my_dbo_table (col1 varchar(10))
> Neither of the following work because I'm missing SELECT privileges:
> select * from my_dbo_table
> select * from dbo.my_dbo_table
> So I try:
> grant select, insert, update,delete on dbo.my_dbo_table to jdbcuser
> But that doesn't work, because I get "Grantor does not have GRANT
> permission." So.......I'm allowed to create objects with dbo. but I
> then
> retain no privileges on them, even though I created them?
> Is the dbowner the only one allowed to grant privs on these objects?
> --
> DK
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
>sql

Question about functions

Hi,
I need to call a function in a sql query in a stored procedure to
calculate time differences between various dates. I have a function
that uses a cursor to sum up the totals of these numbers, but it runs
very, very slowly. I can accomplish the same results without a cursor
by using a temporary table and several queries, but when I try to put
this in a stored procedure and call the stored procedure from the
function, I get the following error:
Only functions and extended stored procedures can be executed from
within a function.
Any suggestions?
Thanks,
Amy Bolden>> Any suggestions?
The message clearly states what you can do with a function. So you will have
to find an alternative to calling procedures from functions. Either make the
calling routine a procedure or make the called routine a function.
Anith|||You must supply more information about that, did you try to use
datediff in a correlated query (don=B4t know where you get the data from
?).
Jens Suessmeyer.|||Sorry, I thought maybe an explanation would be enough.
Here is the top query in the stored procedure. The @.StartDate and
@.EndDate are
parameters that are supplied by a web application:
SELECT DISTINCT(CONVERT(VARCHAR(10), TT.DateTS, 101)) AS ActivityDate,
POSUM.UserName,
TT.UserId,
dbo.fnTimeInTruckInMinutes(TT.UserId,TT.DateTS) AS TimeInTruck
FROM vw_TTLH TT INNER JOIN
UserMaster POSUM ON TT.UserID = POSUM.UserNum
WHERE DateTS BETWEEN @.StartDate AND @.EndDate)
GROUP BY POSUM.UserName, TT.UserID, POSUM.UserNum,
CONVERT(VARCHAR(10), TT.DateTS, 101), TT.DateTS
Here is the function that calculates all the time spent in a truck for a
particular day:
CREATE FUNCTION dbo.fnTimeInTruckInMinutes (@.UserID int = NULL,
@.ActivityDate DateTime = NULL)
RETURNS int
AS
BEGIN
DECLARE @.TotalTimeInTruck INT
Exec spGetTotalTimeInTruck @.UserID, @.ActivityDate, @.TotalTimeInTruck
RETURN @.TotalTimeInTruck
END
Here is the stored procedure that I am trying to call from the function
to get the total time
spent in a truck on a single day:
CREATE PROCEDURE spGetTotalTimeInTruck
@.UserID INT,
@.ActivityDate VARCHAR(10),
@.TotalTimeInTruck INT OUTPUT
AS
DECLARE @.ActivityDatePlusOne VARCHAR(10)
SET @.ActivityDatePlusOne = DATEADD(d, 1, @.ActivityDate)
CREATE TABLE #tempTruck
(
SeqNum int NULL,
InTruck datetime NULL,
OutTruck datetime NULL
)
INSERT INTO #tempTruck
(OrderNumber, InTruck)
SELECT OrderNumber, DateTS
FROM vw_TTLH
WHERE USerID = @.UserID
AND DateTimeStamp >= @.ActivityDate
AND DateTimeStamp <= @.ActivityDatePlusOne
AND ActID in (200)
UPDATE #tempTruck
SET OutTruck = (
SELECT DateTS
FROM vw_TTLH
WHERE USerID = @.UserID
AND DateTimeStamp >= @.ActivityDate
AND DateTimeStamp <= @.ActivityDatePlusOne
AND OrderNumber = #tempTruck.OrderNumber + 1)
SELECT SUM(DateDiff(n, InTruck, OutTruck)) FROM #tempTruck
DROP TABLE #tempTruck
RETURN @.TotalTimeInTruck
Thanks,
Amy Bolden
*** Sent via Developersdex http://www.examnotes.net ***|||Are you talking about something similar to this?
http://www.eggheadcafe.com/articles/20030626.asp
Robbe Morris - 2004/2005 Microsoft MVP C#
Free Source Code for ADO.NET Object Mapper To DataBase Tables And Stored
Procedures
http://www.eggheadcafe.com/articles...e_generator.asp
"Amy" <abolden@.eastridge.net> wrote in message
news:1127400856.741551.164370@.z14g2000cwz.googlegroups.com...
> Hi,
> I need to call a function in a sql query in a stored procedure to
> calculate time differences between various dates. I have a function
> that uses a cursor to sum up the totals of these numbers, but it runs
> very, very slowly. I can accomplish the same results without a cursor
> by using a temporary table and several queries, but when I try to put
> this in a stored procedure and call the stored procedure from the
> function, I get the following error:
> Only functions and extended stored procedures can be executed from
> within a function.
> Any suggestions?
> Thanks,
> Amy Bolden
>|||You might try eliminating the temp table and using a derived table in
its place. Something like this: (COMPLETELY UNTESTED)
CREATE PROCEDURE spGetTotalTimeInTruck @.UserID INT, @.ActivityDate
VARCHAR(10), @.TotalTimeInTruck INT OUTPUT
AS
SELECT -- Should "@.TotalTimeInTruck = " go here'
SUM(DateDiff(n, InTruck, OutTruck)) AS
FROM ( SELECT OrderNumber, DateTS AS InTruck,
( SELECT DateTS
FROM vw_TTLH ttlh2
WHERE USerID = @.UserID
AND DateTimeStamp >= @.ActivityDate
AND DateTimeStamp <= DATEADD(d, 1, @.ActivityDate)
AND OrderNumber = ttlh1.OrderNumber + 1) AS OutTruck
FROM vw_TTLH ttlh1
WHERE USerID = @.UserID
AND DateTimeStamp >= @.ActivityDate
AND DateTimeStamp <= DATEADD(d, 1, @.ActivityDate)
AND ActID in (200)
) ttlh_d
RETURN @.TotalTimeInTruck
You might then consider making the function an inline function (put the
select inline - "return select ..."). The optimizer seems to like that
better than procedural code.
Good luck.
Payson
Amy Bolden wrote:
> Sorry, I thought maybe an explanation would be enough.
> Here is the top query in the stored procedure. The @.StartDate and
> @.EndDate are
> parameters that are supplied by a web application:
> SELECT DISTINCT(CONVERT(VARCHAR(10), TT.DateTS, 101)) AS ActivityDate,
> POSUM.UserName,
> TT.UserId,
> dbo.fnTimeInTruckInMinutes(TT.UserId,TT.DateTS) AS TimeInTruck
> FROM vw_TTLH TT INNER JOIN
> UserMaster POSUM ON TT.UserID = POSUM.UserNum
> WHERE DateTS BETWEEN @.StartDate AND @.EndDate)
> GROUP BY POSUM.UserName, TT.UserID, POSUM.UserNum,
> CONVERT(VARCHAR(10), TT.DateTS, 101), TT.DateTS
> Here is the function that calculates all the time spent in a truck for a
> particular day:
> CREATE FUNCTION dbo.fnTimeInTruckInMinutes (@.UserID int = NULL,
> @.ActivityDate DateTime = NULL)
> RETURNS int
> AS
> BEGIN
> DECLARE @.TotalTimeInTruck INT
>
> Exec spGetTotalTimeInTruck @.UserID, @.ActivityDate, @.TotalTimeInTruck
> RETURN @.TotalTimeInTruck
> END
> Here is the stored procedure that I am trying to call from the function
> to get the total time
> spent in a truck on a single day:
> CREATE PROCEDURE spGetTotalTimeInTruck
> @.UserID INT,
> @.ActivityDate VARCHAR(10),
> @.TotalTimeInTruck INT OUTPUT
> AS
> DECLARE @.ActivityDatePlusOne VARCHAR(10)
> SET @.ActivityDatePlusOne = DATEADD(d, 1, @.ActivityDate)
> CREATE TABLE #tempTruck
> (
> SeqNum int NULL,
> InTruck datetime NULL,
> OutTruck datetime NULL
> )
> INSERT INTO #tempTruck
> (OrderNumber, InTruck)
> SELECT OrderNumber, DateTS
> FROM vw_TTLH
> WHERE USerID = @.UserID
> AND DateTimeStamp >= @.ActivityDate
> AND DateTimeStamp <= @.ActivityDatePlusOne
> AND ActID in (200)
> UPDATE #tempTruck
> SET OutTruck = (
> SELECT DateTS
> FROM vw_TTLH
> WHERE USerID = @.UserID
> AND DateTimeStamp >= @.ActivityDate
> AND DateTimeStamp <= @.ActivityDatePlusOne
> AND OrderNumber = #tempTruck.OrderNumber + 1)
> SELECT SUM(DateDiff(n, InTruck, OutTruck)) FROM #tempTruck
> DROP TABLE #tempTruck
> RETURN @.TotalTimeInTruck
> Thanks,
> Amy Bolden
> *** Sent via Developersdex http://www.examnotes.net ***|||You might try eliminating the temp table and using a derived table in
its place. Something like this: (COMPLETELY UNTESTED)
CREATE PROCEDURE spGetTotalTimeInTruck @.UserID INT, @.ActivityDate
VARCHAR(10), @.TotalTimeInTruck INT OUTPUT
AS
SELECT -- Should "@.TotalTimeInTruck = " go here'
SUM(DateDiff(n, InTruck, OutTruck)) AS
FROM ( SELECT OrderNumber, DateTS AS InTruck,
( SELECT DateTS
FROM vw_TTLH ttlh2
WHERE USerID = @.UserID
AND DateTimeStamp >= @.ActivityDate
AND DateTimeStamp <= DATEADD(d, 1, @.ActivityDate)
AND OrderNumber = ttlh1.OrderNumber + 1) AS OutTruck
FROM vw_TTLH ttlh1
WHERE USerID = @.UserID
AND DateTimeStamp >= @.ActivityDate
AND DateTimeStamp <= DATEADD(d, 1, @.ActivityDate)
AND ActID in (200)
) ttlh_d
RETURN @.TotalTimeInTruck
You might then consider making the function an inline function (put the
select inline - "return select ..."). The optimizer seems to like that
better than procedural code.
Good luck.
Payson
Amy Bolden wrote:
> Sorry, I thought maybe an explanation would be enough.
> Here is the top query in the stored procedure. The @.StartDate and
> @.EndDate are
> parameters that are supplied by a web application:
> SELECT DISTINCT(CONVERT(VARCHAR(10), TT.DateTS, 101)) AS ActivityDate,
> POSUM.UserName,
> TT.UserId,
> dbo.fnTimeInTruckInMinutes(TT.UserId,TT.DateTS) AS TimeInTruck
> FROM vw_TTLH TT INNER JOIN
> UserMaster POSUM ON TT.UserID = POSUM.UserNum
> WHERE DateTS BETWEEN @.StartDate AND @.EndDate)
> GROUP BY POSUM.UserName, TT.UserID, POSUM.UserNum,
> CONVERT(VARCHAR(10), TT.DateTS, 101), TT.DateTS
> Here is the function that calculates all the time spent in a truck for a
> particular day:
> CREATE FUNCTION dbo.fnTimeInTruckInMinutes (@.UserID int = NULL,
> @.ActivityDate DateTime = NULL)
> RETURNS int
> AS
> BEGIN
> DECLARE @.TotalTimeInTruck INT
>
> Exec spGetTotalTimeInTruck @.UserID, @.ActivityDate, @.TotalTimeInTruck
> RETURN @.TotalTimeInTruck
> END
> Here is the stored procedure that I am trying to call from the function
> to get the total time
> spent in a truck on a single day:
> CREATE PROCEDURE spGetTotalTimeInTruck
> @.UserID INT,
> @.ActivityDate VARCHAR(10),
> @.TotalTimeInTruck INT OUTPUT
> AS
> DECLARE @.ActivityDatePlusOne VARCHAR(10)
> SET @.ActivityDatePlusOne = DATEADD(d, 1, @.ActivityDate)
> CREATE TABLE #tempTruck
> (
> SeqNum int NULL,
> InTruck datetime NULL,
> OutTruck datetime NULL
> )
> INSERT INTO #tempTruck
> (OrderNumber, InTruck)
> SELECT OrderNumber, DateTS
> FROM vw_TTLH
> WHERE USerID = @.UserID
> AND DateTimeStamp >= @.ActivityDate
> AND DateTimeStamp <= @.ActivityDatePlusOne
> AND ActID in (200)
> UPDATE #tempTruck
> SET OutTruck = (
> SELECT DateTS
> FROM vw_TTLH
> WHERE USerID = @.UserID
> AND DateTimeStamp >= @.ActivityDate
> AND DateTimeStamp <= @.ActivityDatePlusOne
> AND OrderNumber = #tempTruck.OrderNumber + 1)
> SELECT SUM(DateDiff(n, InTruck, OutTruck)) FROM #tempTruck
> DROP TABLE #tempTruck
> RETURN @.TotalTimeInTruck
> Thanks,
> Amy Bolden
> *** Sent via Developersdex http://www.examnotes.net ***|||Hi
I think if you omitt using of CURSUR in your Function then it will be some
more faster. If you are using cursor for itration purpose than I have an Ide
a
that may help you.
There is no concept of Arrays in SQL Server I think so, but we can create
our own Psedu Arrays, and we can itrate in these arrays.
Define a local variable as varchar and insert the Primary key in the varable
COMMA seprated.
And then by a while loop get the ID and select the field(s) you want from
the orignal table and colculate it.
Run This code It may open your Mind
Declare @.var varchar(100)
SET @.var = '120,20,23,32,23234,,3,5,6,'
WHILE @.var <> ''
BEGIN
Declare @.id int
SET @.id = CAST(SUBSTRING(@.var,0, CharIndex(',', @.var,0)) as int)
SET @.var = SUBSTRING(@.var, CharIndex(',', @.var,0)+1, LEN(@.var))
PRINT @.id
END
________________________________________
__________________
"Amy" wrote:

> Hi,
> I need to call a function in a sql query in a stored procedure to
> calculate time differences between various dates. I have a function
> that uses a cursor to sum up the totals of these numbers, but it runs
> very, very slowly. I can accomplish the same results without a cursor
> by using a temporary table and several queries, but when I try to put
> this in a stored procedure and call the stored procedure from the
> function, I get the following error:
> Only functions and extended stored procedures can be executed from
> within a function.
> Any suggestions?
> Thanks,
> Amy Bolden
>