VIEW showing result of a STORED PROCEDURE ?
Lisa Pearlson
I have to create a 'virtual table' to be used with MS Access (OfficeXP).
I don't have experience with Access much but I know you can import VIEWs,
however not stored procedures to work with.
I have an SQL query that returns multiple rows per company because there are
multiple people working for the company.
Instead, I want these people to be listed behind eachother:
Instead of this:
Company People
Company1 Person1
Company1 Person2
I want this:
Company People
Company1 Person1, Person2
Since I do not think this can be done in Access, I wish to create a query
for it on SQL Server 2000.
I think I must use a cursor to accomplish the above and so it requires a
stored procedure instead of a view, right?
But then how can I get this result in Access? It can import views as a table
but not stored procedures, can it?
That's why I'm wondering if I can create a View to wrap the stored procedure
somehow.
I know this wasn't possible in SQL7, but in SQL2000 it might be.. couldn't
find anything in online docs because I don't know what to look for.
Lisa
oj
UDF to do your concatenation. Then in your stored procedure you would pass the
id to the udf so it can return the concatenated string.
e.g.
use Northwind
go
create function dbo.udf(@i int)
returns varchar(1000)
as
begin
declare @s varchar(1000)
select @s=isnull(@s+',','')+CustomerID
from Orders
where EmployeeID=@i
return @s
end
go
create proc usp
as
set nocount on
select EmployeeID,Customers=max(dbo.udf(EmployeeID))
from Orders
group by EmployeeID
go
exec usp
go
drop function dbo.udf
drop proc usp
go
--
-oj
http://www.rac4sql.net
"Lisa Pearlson" <n...@spam.plz> wrote in message
news:%230kGv1x...@TK2MSFTNGP09.phx.gbl...
Lisa Pearlson
But this is like magic to me. I'm trying to understand it. There are several
things here I'm unexperienced with.
First, the user defined function... how is it different from a stored
procedure?
I'm assuming stored procedures can't be used within select statements.
How about user permissions? Can you create a user function and leave it on
the system like a stored procedure to be used within views or is it more
like a temporary object, like a temporary table, functions are always
temporary?
How about multiuser considerations? Would your code not error out when 2
users almost simultaneously would create the function udf, saying the
function already exists, or does it only exist within the context of a
session?
I have read the online docs but it's still not clear to me.
Second, why the max statement? The result is the same without the max and
alot faster.
Why do you use an assignment Customers=udf(x) rather than an alias udf(x) AS
Customers?
The result is the same and alot faster when you do
select EmployeeID, (dbo.udf(EmployeeId)) AS Customers
instead of:
select EmployeeID,Customers=max(dbo.udf(EmployeeID))
Lisa
"oj" <nospam...@home.com> wrote in message
news:%231sCbAy...@tk2msftngp13.phx.gbl...
Lisa Pearlson
subqueries or something?
I guess not, but it would be nice if it could be used inside a view.
Because the advantage of a view over a stored procedure is that you can
reuse the result in another query as if it were a table of its own. Stored
procedures won't let you do that.. UDF's do, but I guess IDF's won't let you
call a stored procedure or if it does, it won't let you reuse that UDF that
calls a stored procedure, into a new query.
Lisa
Steve Kass
You can define a view to return the output of oj's stored procedure if
you set up your server as a loopback linked server:
create view V as
select * from openquery(desktop,'exec Northwind.dbo.usp')
It's probably a little dicey, and I don't know what issues might arise
in terms of locking, concurrency, etc., though.
SK
oj
1. There are similarities and differences between UDF and SP. The most important
thing to know about udf is that UDF cannot return multiple resultsets. It can
only return *ONE* result whether a scalar (resultset with one column/row) or a
table (resultset with many columns and rows).
2. No, SP cannot be called within a select statement. Though, there's trick to
use the sp like a table - we're not going there.
3. Yes, UDF should be created once and can be used/called by many users
simultaneously. The matter of fact, you do not want to create and then drop the
udf for every user connection. The cost of such activity is way too high.
4. Yes, you can call/use the udf inside your view.
e.g.
create view v
as
select EmployeeID,Customers=max(dbo.udf(EmployeeID))
from Orders
group by EmployeeID
go
5. What you see demonstrated here is a scalar udf. That means it gets called
once for every row in the table. You wouldn't want to return this, would you.
empid cust
----- -----
1 a,b,c
1 a,b,c
1 a,b,c
2 d
3 e,f
3 e,f
The use of this aggregate is to make each returned row *unique*. As stated, the
udf is called once for every row, you can also do this (see if this makes it
easier for you to understand).
e.g.
create view v
as
select EmployeeID,Customers=max(dbo.udf(EmployeeID))
from (select distinct EmployeeID from Orders)derived
go
6. Cust=(...) is the same as (...) as Cust. I often use this old style syntax
because I'm used to it.
--
-oj
http://www.rac4sql.net
"Lisa Pearlson" <n...@spam.plz> wrote in message
news:%23YDbSny...@tk2msftngp13.phx.gbl...
Steve Kass
oj wrote:
>Hmm...where to start. <G>
>
>1. There are similarities and differences between UDF and SP. The most important
>thing to know about udf is that UDF cannot return multiple resultsets. It can
>only return *ONE* result whether a scalar (resultset with one column/row) or a
>table (resultset with many columns and rows).
>2. No, SP cannot be called within a select statement. Though, there's trick to
>use the sp like a table - we're not going there.
>
>
Oops.
Uri Dimant
e.g.
>create view v
>as
>select EmployeeID,Customers=max(dbo.udf>(EmployeeID))
>go
This query does not work because you did not include GROUP BY
EmployeeID.Moreover it will not give you to create a view.
"oj" <nospam...@home.com> wrote in message
news:OgjCA#yBEH...@tk2msftngp13.phx.gbl...
oj
create view v
as
select EmployeeID,Customers=dbo.udf(EmployeeID)
from (select distinct EmployeeID from Orders)derived
go
--
-oj
http://www.rac4sql.net
"oj" <nospam...@home.com> wrote in message
news:OgjCA%23yBE...@tk2msftngp13.phx.gbl...
oj
--
-oj
http://www.rac4sql.net
"Uri Dimant" <ur...@iscar.co.il> wrote in message
news:uzyaaIzB...@TK2MSFTNGP10.phx.gbl...