Solved

SQL 2005 Parameterized Views ?????

Posted on 2006-11-14
4
1,511 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
  • 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

830 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