SQL column true or false - use a view?

Simple question, I have an application and dependant on whether you are in the administration system or on the public website I want to show different results.

Example: in the database for a news story in the administration I may set the column value 'showonsite' to false. So I would like this to show in the administration panel only and not on the live site.

My question is, because I require the same information just with the only one column change, live site to only show true values and administration to show both. What is the most effective way of achieving this without copy paste of code?

Thanks

Answers


Depends on where you write your code - if you use stored procedures, then just a parameter like @liveonly should be sufficient. Sameway if you use an inline sql query directly in a C# method, then a parameter live_only should be good enough, to determine whether the extra where condition will be added or not.

For eg. in the SP

SELECT * 
FROM news
WHERE ((@live_only=true AND showonsite=true) or (@live_only=false))

This way it can be called with @live_only = false for admin panel, but with value true for the actual site.


Please see the second query.

I may be missing something. For non-adminstrative users, could you simply not SELECT news stories with the showonsite column set to false?

For example:

SELECT
    *
FROM
    dbo.NewsStory
WHERE
    showOnSite = 1

Edit:

Oh! I see. You're talking about copying and pasting the query. It's late. >.<

If you have a stored procedure, you can pass a value to indicate whether the given user is an administrator:

SELECT
    *
FROM
    dbo.NewsStory
WHERE
    showOnSite = 1
    OR @isAdmin = 1

Need Your Help

Powershell GUIs - Is there an Active Directory dialog box for selecting an OU? Like the FileOpenDialog?

.net powershell active-directory

Is there a class (possibly exposed through .NET) that allows you to kick off an active directory gui tree list? I have an app where I'm iterating the top level OU structure of a domain and present...

How do I debug custom build processes in TFS 2010?

tfs2010 tfsbuild team-build teambuild2010

I know my build isn't working, but I can't figure out how to debug it. My only symptom is it only does a get operation for some of the projects in the solution. Setting verbosity to diagnostic tel...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.