I could do it with an SP, but it must be a VIEW

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

I could do it with an SP, but it must be a VIEW

Author

Topic

LaurieCox

158 Posts

Posted - 2009-11-20 : 15:45:38

I have a table:

      
create table #Auths (
       AuthId int,
       ClientId int,
       ProviderId int)
insert into #Auths
select 1, 3046, 5200 union all
select 2, 3048, 5200 union all
select 3, 3052, 5200 union all
select 4, 3053, 5200 union all
select 5, 3046, 6200 union all
select 6, 3046, 7326 union all
select 7, 3050, 7326 union all
select 8, 3051, 5101 union all
select 9, 3053, 6800

From this table I must pull (using a VIEW*) for a given provider (ProviderId) all of the auths that they are allowed to see.

A provider is allowed to see all auths (their own and any other providers) for any client (ClientId) that they have a least one auth to.

So if I have a view called AvailableAuths

Create view AvailableAuths
As
/* here goes the magic code*/

Then my expected results are the following:

      
select AuthId,
       ClientId, 
       Provider
  from AvailableAuths
 where ProviderId = 5200

AuthId ClientId ProviderId
====== ======== ========== 
1      3046     5200
2      3048     5200
3      3052     5200
4      3053     5200
5      3046     6200
6      3046     7326
9      3053     6800

select AuthId,
       ClientId, 
       ProviderId
  from AvailableAuths
 where ProviderId = 6200

AuthId ClientId ProviderId
====== ======== ========== 
1      3046     5200
5      3046     6200
6      3046     7326

select AuthId,
       ClientId, 
       ProviderId
  from AvailableAuths
 where ProviderId = 7326

AuthId ClientId ProviderId
====== ======== ========== 
1      3046     5200
5      3046     6200
6      3046     7326
7      3050     7326

*This must be a view. I do not have the ability to use a stored procedure.

Thanks,

Laurie

Edit: because I missed on row in the expected result list where ProviderId = 5200

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-20 : 16:50:07

why not an SP?

see this article: http://msdn.microsoft.com/en-us/library/cc966395.aspx

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-20 : 18:04:54

Hey

You can create a view...which I'm all in favor of, but I'm curious, you can't create a sproc?

So is there is a problem?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-11-21 : 15:05:38

[code]DECLARE @Sample TABLE
(
AuthID INT,
ClientID INT,
ProviderID INT
)
INSERT @Sample
SELECT 1, 3046, 5200 UNION ALL
SELECT 2, 3048, 5200 UNION ALL
SELECT 3, 3052, 5200 UNION ALL
SELECT 4, 3053, 5200 UNION ALL
SELECT 5, 3046, 6200 UNION ALL
SELECT 6, 3046, 7326 UNION ALL
SELECT 7, 3050, 7326 UNION ALL
SELECT 8, 3051, 5101 UNION ALL
SELECT 9, 3053, 6800

SELECT s2.AuthID,
s2.ClientID,
s1.ProviderID
FROM @Sample AS s1
INNER JOIN @Sample AS s2 ON s2.ClientID = s1.ClientID
WHERE s1.ProviderID = 7326
ORDER BY s2.AuthID[/code]

N 56°04'39.26"
E 12°55'05.63"

nalnait
Starting Member

14 Posts

Posted - 2009-11-22 : 04:18:36

u can create a view not include where . in u app u can use view and where condition

LaurieCox

158 Posts

Posted - 2009-11-23 : 09:57:09

Thanks Peso, that should work prefect.

In answer to why a view and not an SP:

We bought a system for managing the clinical and billing data for our consumer base. As we manage services for consumers from outside providers we needed an external app that the providers could use for managing authorization requests. The company that we bought the internal system from developed an external web based system that communicates with the internal system using web services. This system uses .net and a SQLServer backend all hosted by them (i.e. we have no direct access to the database on their SQLServer).

So that we could provide reports for our external providers, the company has made it possible for us to upload Crystal Reports (developed by us) to the external system. But they only expose the base tables to us to pull into the reports (they allow no development of SPs). They did finally agree to implement views that we write.

And that is why it must be a view and not an SP.

Laurie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources