Solved

SQL 2005 Parameterized Views ?????

Posted on 2006-11-14
4
1,512 Views
Last Modified: 2008-01-09
I'm just looking at trying to create a view in SQL 2005 that pulls back data based upon a value.... i.e. :

SELECT     request_item AS [Item Reference], request_by AS [Requested By], requested_on AS [When Requested]
FROM         dbo.PCT_Active_Requests
where request_to = @in_site
---------------------------------------------------------------------------------------------------------------------------------------

Now, I know I can't use parameters in a View, so I'm guessing I need a function. Basically, the 'SITE' argument is calculated on what the user logs into windows as. Again, this is no problem when calling a stored procedure from a .net front end as the system.environment.username is able to be passed as a parameter.

What I'm looking to do, I think....., is to be able to create a view which has a function as the argument for the WHERE statement, but the function needs to be able to know what the user is logged into windows as....we're not planning on using INTEGRATED SECURITY, but if we have to, then we have to....

I hope this makes some sort of sense?

Thanks,

JC
0
Comment
Question by:misdevelopment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 

Author Comment

by:misdevelopment
ID: 17938802
Alternatively - is there anything to stop me using a stored procedure instead of a VIEW entirely???

The view / stored procedure is going to be databound to a dataview in a windows forms app...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17938814
>Now, I know I can't use parameters in a View, so I'm guessing I need a function
correct

you are aware of the function user, system_user ...
0
 

Author Comment

by:misdevelopment
ID: 17938829
If we aren't using integrated security, how will this show me the windows username though?
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 500 total points
ID: 17942723
If you aren't using windows integrated security it won't show the windows user.

I highly recommend using windows integrated security.

You can for example granat a standard windows group SQL Server access.

Then you can still use the SUSER_SNAME() function to return the individual windows login (even they they are granted access through a windows group). When a user needs access to your app you just add them to the windows group (i.e. the standard help desk can do it) and it works automatically. There is no need to add them to SQL Server.

We use this method very successfully here to forever banish the need for users to remeber 93 different logins and passwords for all their different applications (slight exageration)


The handy thing is that you can incorporate this user name into a view, removing the need for passing a paramter in.


For example, if you can tag the request items with the windows user name then create this view:


SELECT     request_item AS [Item Reference], request_by AS [Requested By], requested_on AS [When Requested]
FROM         dbo.PCT_Active_Requests
where request_to_windowsusername = suser_sname()


Then everyone will automatically only see only their own requests when they run this view. There is no need for intermediate logic to capture user names etc.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question